1

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;

1 Answer 1

3

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;
6
  • argument of AND must be type boolean, not type character varying
    – neoo
    Aug 9, 2018 at 19:40
  • Then add a unique (or primary key) or exclusion constraint on name. Aug 9, 2018 at 19:42
  • combination of NAME, ADDRESS forms unique constraint.
    – neoo
    Aug 9, 2018 at 19:50
  • Then you need to use on conflict (name, address) do update. Aug 9, 2018 at 20:34
  • still getting same error: there is no unique or exclusion constraint matching the ON CONFLICT specification
    – neoo
    Aug 10, 2018 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.