This works (see fiddle here):
WITH new_row AS (
INSERT INTO my_table (
) VALUES (
) RETURNING *
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
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
new_row CTE "table" to the
somekey field) and not to the new row (note, no
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.