How to use ON DUPLICATE KEY UPDATE in Postgresql with php

PHP
1

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

sourcecode constraint is : “unique”

2

ON DUPLICATE KEY UPDATE does not exist in PostgreSQL.

cf. http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql

3

ok so how do i work out this problem:

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.

Code

if(array_key_exists(“btnsubmit”, $_POST)) {
$pk_check = “true”;

if($pk_check == "true") {
    $parameters = array();
    $parameters[] = $counter;

    $parameters[] = $_POST['source'];


    $sql_string = "INSERT INTO config.sourcecodes (id,sourcecode ) VALUES ($1,$2 )";


    try {
        $saved = $db_cfg->Execute($sql_string, "", $parameters);


        if($saved) {
            $feedback = 'New Campaign: ' . $_POST['campaign_id'] . ' Saved  successfully';
        } else {
            error_reporting(32);
            $feedback = "_Error Saving Source Code ! _";
            //echo pg_last_notice($sql_string) ;
            //  throw new $exception('Error saving new source code');
        }
    }
    catch(\sdException $exception) {
        throw new Exception('fatal err trycatch nt wrkn');
    }
4

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.

5

Thats a great idea it works but i need it to do nothing when there is a duplicate violation

6

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.

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.