Example:
create table foo(
id serial,
txt text
);
insert into foo(txt) values ('a'),('b'),('c') returning id;
Returns:
id
----
1
2
3
(3 rows)
It seems that the first id
in the return value will always be the id
for 'a'
, the second for 'b'
and so on, but is this defined behaviour of insert into
, or is it a coincidence that may fail under odd circumstances?
You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also required. If a column list is specified, you only need INSERT privilege on the listed columns.
PostgreSQL excludes statements in PostgreSQL is used to compare any two rows from the specified column or expression by using the operator specified in PostgreSQL. At the time of excluding the column, the comparison operator will return the null or false value as output.
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
While the documentation isn't entirely clear, it does state that:
If the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted by the command.
Now "similar to" isn't an ironclad guarantee, and I've raised this for discussion on the mailing list ... but in practice, PostgreSQL won't mess with the order of values in RETURNING
. It's unlikely we'll ever be able to even if we want to for optimisation, because too many apps rely on it being ordered the same as the input.
So... for INSERT INTO ... VALUES (...), (...), ... RETURNING ...
and for INSERT INTO ... SELECT ... ORDER BY ... RETURNING ...
it should be safe to assume that the result relation is the in the same order as the input.
I don't see anything in the documentation that guarantees an order for RETURNING
so I don't think you can depend on it. Odds are that the RETURNING
order will match the VALUES
order but I don't see any guarantees about what order the VALUES
will be inserted in either; the VALUES
are almost certainly going to be insert in order from left to right but again, there is no documented guarantee.
Also, the relational model is set based so ordering is something applied by the user rather than an inherent property of a relation. In general, if there is no way to explicitly specify an ordering, there is no implied ordering.
Execute summary: the ordering you're seeing is probably what will always happen but it is not guaranteed so don't depend on it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With