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?

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)

  • Thank you for the clarification. So there is no other way to get the latest inserted Id with that query than select max( id ) from ( select Id from t1 union all ... from tN )? – bessgeor Dec 19 '17 at 9:31
  • @bessgeor If you need to distinguish between the id inserted in the first statement and the one inserted in the second you need to use two separate statements, not a data modifying CTE (and then you can easily use lastval()) – a_horse_with_no_name Dec 19 '17 at 9:33
  • @a_horse_with_no_name I think you can also chain the CTEs, so the order of their execution is predictable. – ypercubeᵀᴹ Dec 19 '17 at 9:55
  • @a_horse_with_no_name Is there any doc on how database is choosing when to run one statement or another in CTE or is it an implementation detail on which one should not relate? – bessgeor Dec 19 '17 at 9:56
  • 2
    @bessgeor: the simple solution is: don't use a CTE. Use single INSERT statements in the correct order in a single transaction. – a_horse_with_no_name Dec 19 '17 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.