Re: Select query order

In response to Krithinarayanan Ganesh Kumar :
> Hi All,
>
> I am aware that Select query does not guarantee the order of the rows returned
> ( The rows are returned in whatever order the system finds fastest to produce).
>
> Is there any way to SELECT the rows in the same order of insertion ? The
> problem is there is no Primary Key in the table, I am having only a composite
> key. So I cannot ORDER BY pk also.

You can't.

You can use the ctid-column like my example:

test=# create table Krithi ( i int);
CREATE TABLE
test=*# copy krithi from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> \.
test=*# commit;
COMMIT
test=# select ctid, i from krithi ;
ctid | i
-------+---
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(8 rows)

test=*# select ctid, i from krithi order by ctid;
ctid | i
-------+---
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(8 rows)

Okay, looks good, but if you do an update this will fail:

test=*# update krithi set i=5 where i=5;
UPDATE 1
test=*# select ctid, i from krithi order by ctid;
ctid | i
-------+---
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 5
(8 rows)

As you can see, the old tuple (0,5) is deleted and a new (0,9) is
created.

Okay, some more traffic:

test=# insert into krithi values (10);
INSERT 0 1
test=*# select ctid, i from krithi order by ctid;
ctid | i
--------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 5
(0,10) | 10
(9 rows)

test=*# vacuum full krithi ;
ERROR: VACUUM cannot run inside a transaction block
test=!# rollback;
ROLLBACK
test=# vacuum full krithi ;
VACUUM
test=# insert into krithi values (11);
INSERT 0 1
test=*# select ctid, i from krithi order by ctid;
ctid | i
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 11
(9 rows)

Oh, as you can see, our row with i=5 is now on (0,5).

In short: PG has no timestamp or similar for the insert-time for a
record.

But you can use, for instance, a new SERIAL column and order by this
column. Or a timestamp default now().

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99