So I was trying to do an update if a row has been modified and an insert if in my source table has a new record (without update the whole target table). For my surprise in PostgreSQL 9.4 there isn't ON CONFLICT and I can't upgrade my PostgreSQL since it needs to stay in that version. This query only does INSERT once, If I add a new record in my source table (REGION) it doesn't add it on the target table (REGION_TMP), and UPDATE isn't working either.
WITH upsert AS (UPDATE REGION_TMP
SET REGIONID=REGIONID,REGIONDESCRIPTION=REGIONDESCRIPTION
RETURNING *)
INSERT INTO REGION_TMP (REGIONID,REGIONDESCRIPTION) SELECT R.REGIONID,R.REGIONDESCRIPTION FROM REGION R
WHERE NOT EXISTS (SELECT * FROM upsert);
This works fine but for 9.5 version, is there any way I can do a similar thing in the above query?
INSERT INTO REGION_TMP2 (REGIONID,REGIONDESCRIPTION)
SELECT R.REGIONID,R.REGIONDESCRIPTION FROM REGION R
ON CONFLICT(REGIONID)
DO
UPDATE SET (REGIONID,REGIONDESCRIPTION)=ROW (EXCLUDED.*)
WHERE (REGION_TMP2.*) IS DISTINCT FROM (EXCLUDED.*)
RETURNING *;