Check is record exists before inserting

Question

I have a php script to insert new parts into a postgres database. I want to check if the part exists before it is created. How can I do this. I am using the usual INSERT INTO clause.

Answer 1

You SELECT first, to know if you must INSERT or UPDATE, like:

if (
    $db->fetchOne(
        'SELECT COUNT(1) FROM parts WHERE article_number = ?',
        $p->article_number
    )
) {
    $db->update(
        'parts',
        $p->to_array(),
        $db->quoteInto('article_number = ?', $p->article_number)
    );
}
else {
    $db->insert('parts', $p->to_array());
}

Re Milen's comment: great point! With the default PostgreSQL transaction isolation level (READ COMMITTED) it's possible that another process inserts (and commits) "your" part after your SELECT, but before your INSERT.

For the kind of apps we build, we normally just catch the DB exception, and do something about it (inform the user, retry). Or you can go with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. See Transaction Isolation.