Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023 at 23:49, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87(at)gmail(dot)com
>
> >
> > A couple of comments. For the more generic, I prefer RETURNING *
> > you get back all the columns for matching. To me, this solves the
> > problem in a very generic way.
>
> From what I gather from the conversation RETURNING is the red herring.
>
> The request is that for:
>
> INSERT INTO some_table(char_fld) VALUES('a'), ('b'), ('c')
>
> where some_table has an auto increment field that the values created for
> said field will always be done in the order that VALUES data was
> presented so:
>
> SELECT id, char_fld from some_table will always return:
>
> (1, 'a')
> (2, 'b')
> (3, 'c')

It's actually for an insert query like this

INSERT INTO t(data)
SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
num) ORDER BY num

Or even (but the above would be nicer)

INSERT INTO t(id, data)
SELECT nextval(pg_get_serial_sequence('t', 'id')) data
FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, num)
ORDER BY num

Federico
>
> > But SQL (and SET THEORY) basically imply you cannot trust the sequencing
> > of a set of transactions. Parallel execution is just a great simple
> > example.
> >
> > Secondarily, many frameworks I've worked with (and custom ones
> > developed) would actually call the SEQUENCE.NEXTVAL, and assign the IDs,
> > in memory, accepting that we would have gaping holes if some
> > transactions were never actually sent to the server. We did this a lot
> > in master-detail GUI type stuff. It's just easier. The children knew
> > their parent ID, and all the children ID's were effectively known before
> > committing. It made for simple code that never failed.
> > (for large datasets we would want one query that returned a set of IDs,
> > we could order that. And apply it to the records we were about to
> > insert). [Be Careful with GENERATED ALWAYS pks to OVERRIDE]
> >
> > HTH
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com