Re: Guidance on INSERT RETURNING order

On Wed, 12 Apr 2023 at 11:46, Thorsten Glaser <tg(at)evolvis(dot)org> wrote:
>
> On Tue, 11 Apr 2023, Federico wrote:
>
> >My understanding was that they are generated in select order
>
> But are they? (I don’t know, but I’d not assume that.)

That's kind of the point for this question, to see if that's correct or not.

> >If my understanding is incorrect, would this alternative guarantee the above
>
> > 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
> > RETURNING id
>
> Wouldn’t, at that point, it be better to just send multiple
> individual INSERT statements? The overhead (on both sides)
> for all mentioned… workarounds… surely is larger than that?

No, not by a long shot. Sending thousands of single inserts
sequentially over the network requires a lot more time even when doing
that on localhost.
Using a single statement is many times faster.

Federico

> bye,
> //mirabilos
> --
> 15:41⎜<Lo-lan-do:#fusionforge> Somebody write a testsuite for helloworld :-)
>
>
>
>