PostgreSQL not SELECTing by RETURNING value of INSERT

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 ;)

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.