2

I have a query that's supposed to insert a new row, then return it. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after the INSERT.

In this simplified example id is a SERIAL:

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT
   my_table.*,
   other_table.*
FROM my_table
LEFT JOIN other_table ON other_table.somekey = my_table.id

WHERE my_table.id = (SELECT id FROM new_row)

The result is an empty set. Apparently WHERE doesn't work.

If I just do this:

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)

SELECT id FROM new_row;

then I get the value of id correctly.

Why doesn't it work in the WHERE condition?

New contributor
Tamás Polgár is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1

The basic problem is that the insert (that happens in the CTE) in not visible (via the my_table snapshot) in the main query at that time.

So you could finish first the CTE and then select for example from a temporary table

CREATE tABLE my_table(id serIAL,some_row
 INT, some_other_row varchar(199))
WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING id
)
SELECT new_row.id INTO films_recent  FROM new_row;
SELECT
   my_table.*
FROM my_table
WHERE my_table.id = (SELECT id FROM films_recent)
1 rows affected

id | some_row | some_other_row
-: | -------: | :-------------
 1 |        0 | whatever      

db<>fiddle here


Or use new_row in the main query, which makes the insert changes visible!:

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    0,
    'whatever'
  ) RETURNING *
)
SELECT
   new_row.*
FROM new_row ;

It's like a schrödinger cat, the new row can be visible or not ;)

2
  • Basically we can see in the question, that the insert was not executed when the SELECT statement was done. We see also that A SELECT * FROM new_rrowyields a result., so it is i a schrödinger cat problem, that an nsert was wade but is nit in the Database , but in the scope of the CTE still gives a result, this is like a transaction that wasn't comitted, if there is a better Solution in wording, ok change the answer, but it is still correct – nbk May 4 at 9:32
  • Made an edit, please review. I like the schrödinger cat analogy! – ypercubeᵀᴹ May 4 at 11:37
1

This works (see fiddle here):

WITH new_row AS (
  INSERT INTO my_table (
    some_row,
    some_other_row
  ) VALUES (
    4000,
    'sor_400'
  ) RETURNING *
)
SELECT
   mt.id AS mt_id, mt.some_row AS mt_sr, mt.some_other_row AS mt_sor,
   ot.somekey AS ot_sk, ot.other_stuff AS ot_os
FROM new_row mt
LEFT JOIN other_table ot
  ON ot.somekey = mt.id
;

Note the ordering of the tables - put new_row at the beginning in the FROM clause.

Why? It's apparently due to the fact that in a WITH clause, only changed/new values from RETURNING are visible within that statement. At the point at which you try to match with the new row in my_table, it isn't visible yet within the statement.

You have to JOIN the id in new_row CTE "table" to the other_table (somekey field) and not to the new row (note, no _) in my_table (which isn't visible yet).

In other words, using the newly inserted results (new_row) instead of the table snaphsot (my_table) solves this because the snapshot does not include the inserted rows -- it was taken before the query started and thus before the insert.

See WITH Queries (Common Table Expressions)

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.


I find that the most helpful way of thinking about this is to consider the entire thing - CTE(s) + final (main) SQL - as all part of one statement (one query - even though there may be many SELECTs and/or other clauses) and not as one transaction. This means that the underlying tables on disk won't be updated until that statement/query commits at the very end of the whole thing - and we can see this when we query the table in a new section after INSERTing in the previous section.

0

Your Answer

Tamás Polgár is a new contributor. Be nice, and check out our Code of Conduct.

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.