How to use INSERT INTO RETURNING value as a subquery?

Question

I am trying to use the return value of a subquery in another query, but there is an error when using INSERT INTO as a subquery

SELECT * FROM (INSERT INTO items (name) VALUES ("test") RETURNING id);

The error message says:

ERROR:  syntax error at or near "INTO"

How to fix this?

nbk · Answer

you could use a CTE for that

But don't use in Postgres doubole quotes for text, it thinks that is a column, use single quotes instead

CREATE TABLe items(id SERIAL, name varchar(29))

CREATE TABLE

WITH INSERT_CTE as (INSERT INTO items (name) VALUES ('test') RETURNING id)
SELECT id FROM INSERT_CTE;

id
1

SELECT 1