0

Context

Schema setup

create table users( id serial primary key );

Query 1

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1;

Expectations

1

Result

1

Query 2

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t2;

Expectations

2

Result

1

Query 3

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1
union all
select id from t2;

Expectations (taking in account results of Query 2)

1
1

Result

1
2

Question

What's wrong with my expectations and how to get the expected value from Query 2?

bessgeor
  • 3
  • 1

1 Answers1

2

This is an expected and documented behaviour

Quote from the manual

Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable

In your second query the database apparently chose to run t2 before t1 and thus it returns 1.

In the third query there have to be two different values, because that's what a sequence guarantees: it never generates the same number twice (unless you have enabled cycle)