Error: There is a column named in table "*SELECT*", but it cannot be referenced from this part of the query

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?

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 as a on a psuedo-table EXCLUDED. You want to do this instead,

INSERT INTO foo
  SELECT 5 AS z
ON CONFLICT (a)
DO UPDATE SET b = EXCLUDED.a;

For more information see the docs on INSERT,


The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

StackExchange Database Administrators Q#239064, answer score: 4