im getting syntax errors in sql string when adding the ON DUPLICATE KEY UPDATE to my string it not sure how its rewritten as a POSTGRESQL/SQL string
CODE
" $sql_string = "INSERT INTO config.sourcecodes (sourcecode ) VALUES ($1 ) ON DUPLICATE KEY UPDATE
sourcecode = $1";"
i get this error: "Warning:
pg_prepare() [function.pg-prepare]: Query failed: ERROR: syntax error at
or near “ON” LINE 1 " when adding this “ON DUPLICATEKEY UPDATE
sourcecode = $1” to my insert query
i am trying to catch this error in a php webapp that captures input
data from a user. im using php and pgsql on mypgadmin, sourcecode column
constraints : “CONSTRAINT sourcecodes_sourcecode_key UNIQUE (sourcecode)”
ERROR
Warning: pg_execute() [function.pg-execute]: Query failed: ERROR:
duplicate key value violates unique constraint
“sourcecodes_sourcecode_key” DETAIL: Key (sourcecode)=(Source3) already
exists. in D:\xampp\htdocs …php on line 289
Problem when user enters an existing sourcecode then the error is generated on POSTING the value.
PostGres will have Upsert in 9.5. Until then,
IF your code is not likely to have a race condition (multiple people pushing the button at the same time), you can do it this way:
UPDATE table SET sourcecode=$2 WHERE id=$1;
INSERT INTO table (id, sourcecode)
SELECT $1, $2
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=$1);
If the ID hasnt been inserted yet, then the first line will do nothing; the second will create it
If the ID has been inserted, the first line will update it, and the second will find an entry for ID and not do anything.
To do NOTHING on duplicate, simply dont run the first line. Note that this will always return a ‘success’ result for the Insert, it will simply affect 0 rows on duplicate.