Re: Select query order

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Select query order
Date: 2010-03-24 12:22:11
Message-ID: 20100324122211.GA15442@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2010-03-24 12:29:47 Re: Select query order
Previous Message Sean Davis 2010-03-24 12:13:42 Re: Select query order