"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tuesday, June 12, 2018, Steve Krenzel <sgk284@gmail.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.
