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

Thank you all! Your answers cleared this up for me.

Tom, in particular, that reference commit removed any ambiguity for me.
Thank you. It's much appreciated.

On Tue, Jun 12, 2018 at 7:27 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "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
>