UPSERT with ON CONFLICT using values from source table in the UPDATE part

Question

Given:

CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);

CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);

This query:

insert into table_b (pk_b, b) 
select pk_a,a from table_a 
on conflict (b) do update set b=a;

causes the following error:

ERROR:  column "a" does not exist
LINE 1: ...elect pk_a,a from table_a on conflict (b) do update set b=a;
                                                                 ^
HINT:  There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.

How to do the update while referring to content of table_a?

Answer 1

Multiple problems.
Your setup, extended:

CREATE TABLE a (
  pk_a int PRIMARY KEY 
, a int
, comment text  -- added column to make effect clear
);

CREATE TABLE b (
  pk_b int PRIMARY KEY
, b int 
, comment text
);

INSERT INTO a VALUES (1, 11, 'comment from a')
                   , (2, 22, 'comment from a');

INSERT INTO b VALUES (1, 77, 'comment from b');

This works:

INSERT INTO b (pk_b, b, comment) 
SELECT pk_a, a, comment
FROM   a 
ON     CONFLICT (pk_b) DO UPDATE  -- conflict is on the unique column
SET    b = excluded.b;            -- key word "excluded", refer to target column

Result:

TABLE b;

 pk_b | b  |    comment
------+----+----------------
    1 | 11 | comment from b   -- updated
    2 | 22 | comment from a   -- inserted

The problems

0. You are confusing table_a and A in your demo (like @Abelisto commented).

   Using legal, lower-case, unquoted identifiers helps to avoid confusion.

1. Like @Ziggy mentioned, ON CONFLICT only works for actual unique or exclusion constraint violations. The manual:

   The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

   Consequently, ON CONFLICT (b) cannot work, no constraint there. ON CONFLICT (pk_b) works.

2. Like @Ziggy also mentioned, source table names are not visible in the UPDATE part. The manual:

   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.

   Bold emphasis mine.

3. You also cannot use column names of the source table in the UPDATE part. It must be column names of the target row. So you really want:

    SET    b = excluded.b
   

   The manual once more:

   Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.

Answer 2

When doing upserts in PostgreSQL 9.5+ you must refer to the excluded data (that which failed to insert) by the alias excluded. Also, the on conflict option must refer to the key: (pk_b) rather than (b). Eg.

insert into table_b (pk_b, b) 
select pk_a,a from table_a 
on conflict (pk_b) do update set b=excluded.b;

For more information refer to the official documentation or this easy introduction to upsert.