'order by' in an insert into command

[Mike Nolan]

I have the following insert to populate a new table:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno;

I need to access this data in a particular order which may change over
time but the initial order I want is in the order by clause.

The problem is, I'm not getting the data into the right order based
on the sequence values being inserted:

tsecrtddt tseceventid tsecsecno seq

2004-08-30 | 20040731910 | 1 | 356270 ### out of sequence
2004-07-08 | 20040531897 | 2 | 360792
2004-06-03 | 20040425023 | 1 | 354394
2004-04-23 | 20040320702 | 1 | 353557
2004-02-18 | 20040117178 | 2 | 359387 ### out of sequence
2004-01-10 | 20031213418 | 1 | 351315

I can't tell whether this is because the order by clause in the insert
is being ignored or because the sequence is incrememted before the sort
takes place. Is there a way to do this insert?
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jean-Luc Lachance]

Try:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from (
select tseceventid, tsecsecno, tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno) as ss;


Mike Nolan wrote:

I have the following insert to populate a new table:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno;

I need to access this data in a particular order which may change over
time but the initial order I want is in the order by clause.

The problem is, I'm not getting the data into the right order based
on the sequence values being inserted:

tsecrtddt tseceventid tsecsecno seq

2004-08-30 | 20040731910 | 1 | 356270 ### out of sequence
2004-07-08 | 20040531897 | 2 | 360792
2004-06-03 | 20040425023 | 1 | 354394
2004-04-23 | 20040320702 | 1 | 353557
2004-02-18 | 20040117178 | 2 | 359387 ### out of sequence
2004-01-10 | 20031213418 | 1 | 351315

I can't tell whether this is because the order by clause in the insert
is being ignored or because the sequence is incrememted before the sort
takes place. Is there a way to do this insert?
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Mike Nolan wrote:

I have the following insert to populate a new table:

insert into pending_tnmt_sec
select tseceventid, tsecsecno,
nextval('sec_seq'),
tsecrtddt
from tnmtsec
order by tsecrtddt,tseceventid,tsecsecno;

I need to access this data in a particular order which may change over
time but the initial order I want is in the order by clause.

The problem is, I'm not getting the data into the right order based
on the sequence values being inserted:

In your example, I would expect the nextval() to be called during the
"fetch", before the ordering. You could probably do something like:

INSERT INTO pending_tnmt_sec
SELECT foo.*, nextval('sec_seq') FROM
(
SELECT tseceventid, ...
ORDER BY tsecrtddt,tseceventid,tsecsecno
) AS foo
;

I'm not sure whether the SQL standard requires the ORDER BY to be
processed in the sub-select. From a relational viewpoint, I suppose you
could argue that ordering is strictly an output feature.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Richard Huxton <de*@archonet.com> writes:

In your example, I would expect the nextval() to be called during the
"fetch", before the ordering. You could probably do something like: INSERT INTO pending_tnmt_sec
SELECT foo.*, nextval('sec_seq') FROM
(
SELECT tseceventid, ...
ORDER BY tsecrtddt,tseceventid,tsecsecno
) AS foo
; I'm not sure whether the SQL standard requires the ORDER BY to be
processed in the sub-select. From a relational viewpoint, I suppose you
could argue that ordering is strictly an output feature.

I believe the SQL standard disallows this entirely, precisely because it
considers ordering to be strictly an output feature. Postgres will take
it though (in recent releases), and should produce the results Mike wants.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html