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, [email protected]) 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 will help — casraf
– casraf, Commented May 21, 2014 at 8:19

Patrick · Accepted Answer · 2014-05-21 08:20:11Z

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, 2014 at 8:20

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

User_T Over a year ago

Just remembered that i dont use $q and i remove the line, but still getting the same error.

Patrick Over a year ago

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.

User_T Over a year ago

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?

Patrick Over a year ago

Uhm..., dunno. The error must come from somewhere else. What does db_error_out() do? That is not a PHP function right?

User_T Over a year ago

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.

|

Collectives™ on Stack Overflow

How to INSERT 1 row into POSTGRES_DB without unique constraint violation

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related