'order by' in an insert into command

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

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:

Richard Huxton <dev@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