Thread: I have a select statement on the issue.

I have a select statement on the issue.
Following is the process I operate in four steps totally:
Step 1:Create Table
CREATE TABLE test
(
  code character varying(32) NOT NULL,
  name character varying(32) NOT NULL DEFAULT ''::character varying,
  qty integer NOT NULL DEFAULT 0,
  CONSTRAINT pk_test PRIMARY KEY (code)
)
Step 2:Insert Data
insert into test(code,name,qty) values('1001','1001name','qty');
insert into test(code,name,qty) values('1002','1002name','qty');
insert into test(code,name,qty) values('1003','1003name','qty');
Step 3:Select Data
select * from test
Results:
code name     qty
1001 1001name  1
1002 1002name  2
1003 1003name  3
Step 4:Update Date
update test set name='1111name' where code='1002'
Results:
code name     qty
1001 1001name  1
1003 1003name  3
1002 1111name  2

Question:
1. Why the default output changes after I execute the update statement?
2. Qustion, sorting as main keys when query, how to do?
Thank you.

On Nov 28, 2007 1:58 AM,  <gongzhixiao@gmail.com> wrote:
> 1. Why the default output changes after I execute the update statement?
> 2. Qustion, sorting as main keys when query, how to do?

See:
http://www.postgresql.org/docs/8.2/static/queries-order.html

Hi,

On Mittwoch, 28. November 2007, gongzhixiao@gmail.com wrote:
| Step 4:Update Date
| update test set name='1111name' where code='1002'

Simplified, when you perform an update, PostgreSQL internally marks
the affected row as deleted and inserts a new row in the table. For details
look at the MVCC documentation, eg.

  http://www.postgresql.org/docs/8.2/interactive/mvcc-intro.html

| Results:
| code name     qty
| 1001 1001name  1
| 1003 1003name  3
| 1002 1111name  2
|
| Question:
| 1. Why the default output changes after I execute the update statement?

See above; output order is not guaranteed without order clause.

| 2. Qustion, sorting as main keys when query, how to do?

You mean: select * from test order by code ?


Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----