How to INSERT 1 row into POSTGRES_DB without unique constraint violation

Question

I'm trying to write an "INSERT" into a webpage using phpfor a postgres database. This is to input some data from a registration form written in PHP. Currently i have this code in PHP:

function insert($table, $data)
{
global $db;
    if ($table and is_array($data) and !empty($data)) 
    {
        $columns = implode(',',array_keys($data));
        $values = implode(',', escape($data));
        $sql = "INSERT INTO {$table} ($columns) VALUES ($values)";
        $q = pg_query($db, $sql) or db_error_out();
        $result = pg_query($db, $sql) or db_error_out();
        $insert_row = pg_fetch_row($result);
        $id = $insert_row[0];
        return ($id > 0) ? $id : FALSE;
    } else {
        return FALSE;
    }

}

The code works and is inserting the row into postgres DB but i also get this error:

Warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint "index_clients_on_first_name_and_last_name_and_phone_and_email" 
DETAIL: Key (first_name, last_name, phone, email)=(test, test, 5461230, test@mail.com) already exists. in C:\xampp\htdocs\webpage_dev\system\database.php on line 165

Line 165 is the $result, where i know that $db works correctly so i assume that $sql is faulty.

My question is how to get rid of this error and help with the INSERT.

On MySQL, there's `INSERT ... ON DUPLICATE KEY UPDATE`, but in Postgre you're in a problem. Maybe [this](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) will help — casraf, May 21 '14 at 08:19

score 1 · Answer 1 · answered May 21 '14 at 08:20

1

You are executing the query twice:

...
$q = pg_query($db, $sql) or db_error_out();
$result = pg_query($db, $sql) or db_error_out();
...

$q succeeds, then $result fails because the data is already in the database.

answered May 21 '14 at 08:20

Patrick

29,357
6
62
90

Just remembered that i dont use $q and i remove the line, but still getting the same error. – User_T May 21 '14 at 08:26
1

Otherwise the code looks ok, so the most plausible explanation is that the data you are trying to insert is already in the database. Are you sure you insert unique names? Try with some random strings. If that fails, the problem might be elsewhere in your code. – Patrick May 21 '14 at 08:46
When i check the database, the inserted row is there. I tried with different strings like you said all the test rows i tried end up in the DB like it should but im still getting the duplicate error. Any ideas or suggestions about doing it differently? – User_T May 21 '14 at 08:59
Uhm..., dunno. The error must come from somewhere else. What does db_error_out() do? That is not a PHP function right? – Patrick May 21 '14 at 09:11
db_error_out is another function just to display backtrace of the faulty sql's AKA just a better error showing function that doesnt matter at this point. The problem is writing a functional INSERT in postgresql that im having problems with. – User_T May 21 '14 at 09:23
Is it possible that the function `insert()` gets called twice with the same `$data`? – Patrick May 21 '14 at 09:35

How to INSERT 1 row into POSTGRES_DB without unique constraint violation

1 Answers1