Postgres upsert using results from select

Question

I'm trying to upsert with postgres using values from a select. It looks like:

INSERT INTO foo (a, b, c)
SELECT a_, b_, c_
-- hairy sql
ON CONFLICT (...condition...) DO UPDATE
SET "c"=???

On conflict, I want to use one of the values from my select statement, but I can't find the right syntax to alias it. How can I do this with Postgres?

Answer 1

Use the excluded keyword:

INSERT INTO foo (a, b, c)
SELECT a_, b_, c_
-- hairy sql
ON CONFLICT (...condition...) DO UPDATE
  SET c = excluded.c;