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');
INSERT INTO b (pk_b, b, comment)
SELECT pk_a, a, comment
ON CONFLICT (pk_b) DO UPDATE -- conflict is on the unique column
SET b = excluded.b; -- key word "excluded", refer to target column
pk_b | b | comment
1 | 11 | comment from b -- updated
2 | 22 | comment from a -- inserted
You are confusing
A in your demo (like @Abelisto commented).
Using legal, lower-case, unquoted identifiers helps to avoid confusion.
Like @Ziggy mentioned,
ON CONFLICT only works for actual unique or exclusion constraint violations. The manual:
ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.
cannot work, no constraint there.
ON CONFLICT (b)
ON CONFLICT (pk_b) works.
Like @Ziggy also mentioned, source table names are not visible in the
UPDATE part. The manual:
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
Bold emphasis mine.
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.