Assume we have a simple table,
CREATE TABLE foo ( a int PRIMARY KEY, b int );
I'm wanting to write a complex query inside and refer to that query
INSERT INTO foo SELECT 5 AS z ON CONFLICT (a) DO UPDATE SET b = z;
However when I execute that I'm getting an error like this,
ERROR: column "z" does not exist LINE 4: DO UPDATE SET b = z; ^
HINT: There is a column named "z" in table "SELECT", but it cannot be referenced from this part of the query.
Is there a way to make a structure like this work?