How to select newly inserted row inserted in WITH CTE?

Question

I am trying to select a newly inserted row within CTE. Let's say I have a table like this:

CREATE TABLE IF NOT EXISTS foo (
    name TEXT NOT NULL
);

And I run the following query:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

SELECT * FROM foo;

INSERT INTO foo(name) VALUES('bar');

SELECT * FROM foo;

COMMIT;

On the second select I can find the row with 'bar'. But when I change my query to this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

SELECT * FROM foo;

WITH ins AS (
  INSERT INTO foo(name) VALUES('bar') RETURNING *
)
SELECT * FROM foo, ins;

COMMIT;

I cannot select the newly created row in foo. Why is that?

Dbfiddle

Answer 1

I cannot select the newly created row in foo. Why is that?

Because this is how the data modifying statements were designed in Postgres.

Quote from the manual

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

^{(emphasis mine)}