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?
Best Answer
PostgreSQL doesn't see the column by the alias you've given it. That's not how it's parsed. It sees the
z
by the name you've tried to insert it asa
on a psuedo-tableEXCLUDED
. You want to do this instead,For more information see the docs on
INSERT
,