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.

Answer 2

If you rarely expect conflicts: Take the optimistic approach and just insert the new one and handle the uniqueness violation if it occurs. This will invalidate the current transaction when it fails, so if you're doing a lot of work in it, it'll be worthwhile to check first. In that case, I prefer SELECT EXISTS(SELECT 1 ... FROM ... WHERE ...).

Anyway, you must handle the possibility for a uniqueness violation. Even if you check first, there is a possibility that a concurrent transaction inserts the same record and commits first.

Answer 3

insert into parts(article_number,category) 
select 'KEYB','COMPTR' 
where not exists(select * from parts where article_number = 'KEYB')