Re: Semantics around INSERT INTO with SELECT and ORDER BY.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Tuesday, June 12, 2018, Steve Krenzel <sgk284(at)gmail(dot)com> wrote:
>> This is relevant for tables that have a column with a SERIAL type, I need
>> to guarantee that the relative ordering remains the same as the ordering of
>> the selected result set.

> The logical insertion order, and thus the sequence values, will be assigned
> according to the order by.

What is actually going to happen, given say

create table targ (d text, id serial);

insert into targ select x from src order by y;

is that you're going to get a parse tree equivalent to

select x, nextval('targ_id_seq')
from (select x from src order by y) ss;

and then it's a question of whether the planner is capable of reordering
the steps into something you don't want. I think that the presence of
the explicit "ORDER BY" in the sub-select will prevent flattening of the
sub-select, which is enough to make it safe. However, if for some reason
you did not say "ORDER BY" but nonetheless expected the serial values to
get assigned in the same order that the underlying query would produce
rows natively, you might get burnt.

As of 9.6, there are more guarantees in this area than there used
to be (cf commit 9118d03a8), but I don't think it matters as long
as you write an ORDER BY.

regards, tom lane