Search Postgresql Archives

Re: Guidance on INSERT RETURNING order

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 12 Apr 2023 at 23:49, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
>
> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87@xxxxxxxxx
>
> >
> > 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.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux