Understanding INSERT ... RETURNING

Given:

$psql -U postgres
Password for user postgres: 
psql (12.1, server 9.6.2)

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          |

I then ran:

postgres=# select count(*) from foo;
 count 
-------
     0
(1 row)

postgres=# insert into foo (a) values (1) returning (select count(*) from foo);
 count 
-------
     0
(1 row)

INSERT 0 1

Why did it return 0 rather than 1, i.e. since I inserted one row?

The select count(*) from foo inside the returning clause is evaluated before the insert, and then treated as a constant in the returning clause, as explain clearly shows:

> explain insert into foo (a) values (1) returning (select count(*) from foo);
┌───────────────────────────────────────────────────────────────────────────┐
│                                QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────────┤
│ Insert on foo  (cost=41.88..41.89 rows=1 width=4)                         │
│   InitPlan 1 (returns $0)                                                 │
│     ->  Aggregate  (cost=41.88..41.88 rows=1 width=8)                     │
│           ->  Seq Scan on foo foo_1  (cost=0.00..35.50 rows=2550 width=0) │
│   ->  Result  (cost=0.00..0.01 rows=1 width=4)                            │
└───────────────────────────────────────────────────────────────────────────┘
(5 rows)

This is confirmed by the documentation where it says that the returning clause may contain column names or value expressions (using those columns) to be returned (saving a query after the insert):

> select count(*) from foo;
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
(1 row)

> insert into foo (a) values (1) returning 'a', a, (select count(*) from foo);
┌──────────┬───┬───────┐
│ ?column? │ a │ count │
├──────────┼───┼───────┤
│ a        │ 1 │     1 │
└──────────┴───┴───────┘
(1 row)

INSERT 0 1

StackExchange Database Administrators Q#285177, answer score: 5