On duplicate key query

Question

I'm trying to execute the following sql query on postgres:

INSERT INTO Works_On (ESSN,PNo,Hours) VALUES ('199112111',3,3)
ON DUPLICATE KEY UPDATE Hours = 3;

But when I do I get an error which follows:

psql:test2.sql:2: ERROR:  syntax error at or near "ON"
LINE 2: ON DUPLICATE KEY UPDATE Hours = 3;
        ^

I can't really find the error, help would be most appreciated!

You are using MySQL syntax on Postgres. That is why you are getting the error. — Gordon Linoff, Mar 15 '14 at 12:29
with `psql` it's looks like you are using postgresql. Added the same tag. Moreover there is no `ON DUPLICATE KEY UPDATE` in postgresql. See this related post which may be of your help http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql — Rahul, Mar 15 '14 at 12:31

score 2 · Accepted Answer · answered Mar 15 '14 at 12:52

ON DUPLICATE KEY UPDATE is not available in postgresql. You can run the following query.

UPDATE Works_on SET Hours = 3 WHERE ESSN='199112111' AND PNo=3;
INSERT INTO table (id, field, field2)
       SELECT '199112111',3,3
       WHERE NOT EXISTS (SELECT 1 FROM Works_on WHERE ESSN='199112111' AND PNo=3);

Here , UPDATE query will succeed if there is already a row with ESSN='199112111' AND PNo=3

INSERT query will succeed if row with ESSN='199112111' AND PNo=3 does not exist.

On duplicate key query

1 Answers1