Re: Select query order - Mailing list pgsql-novice

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