On Thu, Jan 11, 2024 at 11:55 AM Russell, John <johrss@amazon.com> wrote:
``` postgres=> insert into generatedfields (x) values (0), (10), (100) order by 2 desc returning id, x; ERROR: ORDER BY position 2 is not in select list LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret... ^ ```
Is the acceptance of ORDER BY documented anywhere?
VALUES, like SELECT, is an SQL Command in its own right.
That is what you are ordering, before attempting insertion. Hence why it only sees one column.
I didn’t see that anywhere in the INSERT syntax. Does it have any practical effect if there’s no RETURNING clause, e.g. do the rows get physically inserted in the ORDER BY order, which could have implications for columns like SERIAL?
At present, the order of rows presented to the insert does in no way compel the insert command to act on the provided rows in order; even though in practice it will seem to do so.