Re: INSERT INTO .. SELECT nextval() ORDER BY - returns unexpectedly ordered values

Sjon Hortensius <sjon(at)hortensius(dot)net> writes:
> INSERT INTO test2 SELECT name, nextval('tmp'), id FROM test ORDER BY id ASC;

> I have worked around this by clustering the old table on the new id before
> SELECTing but this behavior doesn't seem to be documented, is this a bug?

No. You're assuming that the nextval() happens after the row ordering,
but this is not necessarily so --- indeed, a strict reading of the SQL
standard would imply that it should *never* be so, because the standard
execution model is that ORDER BY happens after computing the SELECT list.
(Without that, locutions like "ORDER BY 1" would make no sense.) It might
accidentally work if the ORDER BY were done via an indexscan rather than
an explicit sort step, but that's not possible in your test case.

Try something like this to force the evaluation order:

INSERT INTO test2
SELECT name, nextval('tmp'), id FROM
(SELECT name, id FROM test ORDER BY id ASC) ss;

regards, tom lane