How does Insert Into On Conflict Excluded Work?

Question

I can't find any good enough documentation on this, so I just thought I'd ask the Citizens of Stackoverflow.

Let's say I have the following SQL query:

INSERT INTO PUBLIC.TABLE_A (id, user, active, last_login) --The ID column is the primary key
SELECT
     b.id, b.user, b.active, b.last_login
FROM
     PUBLIC.TABLE_B as B
ON CONFLICT ON CONSTRAINT ID_PKEY 
DO UPDATE
     user = excluded.user,
     active = excluded.active,
     last_login = excluded.last_login

Question 1) user, active, and last_login come from PUBLIC.TABLE_A, correct?

Question 2) excluded.user, excluded.active, excluded.last_login are the SAME EXACT records coming from PUBLIC.TABLE_B correct?

Answer 1

Your syntax is incorrect: it should be ON CONFLICT ... DO UPDATE SET "user" = EXCLUDED."user".

The SET points in the direction of the answer, since it is like the SET in an UPDATE statement. Yes, the columns to the left of the = (and which you cannot qualify with a table name) are the columns of the table that is to be updated, which is public.table_a in your case.

The pseudo-table EXCLUDED designates the row that you originally tried to insert, but that was rejected because of the conflict. In your case, that is a row from public.table_b.