Postgresql on conflict throws error

postgresql

I am trying to insert rows when target table doesn't match with stage.
I used the following code which seems correct but I am getting error as:

missing FROM-clause entry for table "S"

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS 
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = S.NAME
AND ADDRESS = S.ADDRESS;

Best Answer

You need to use excluded as the table alias in the do update section, as follows:

INSERT INTO app.CUSTOMERS(NAME, ADDRESS)
SELECT NAME, ADDRESS 
FROM STAGE.STG_CUSTOMERS S
ON CONFLICT (NAME) do update
SET NAME = EXCLUDED.NAME
AND ADDRESS = EXCLUDED.ADDRESS;
Related Question