PostgreSQL not SELECTing by RETURNING value of INSERT 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?
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&lt;&gt;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 ;)