Re: Question on doc for RETURNING clause

On Thu, Jan 11, 2024 at 11:55 AM Russell, John <johrss(at)amazon(dot)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.

https://www.postgresql.org/docs/current/sql-values.html

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.

David J.