0

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.