Checking for a record before inserting

Question

I have a php script to insert new parts into a postgres database. I want to check if a part exists before its creation. How can i do this. I use the usual INSERT INTO clause.

Answer 1

First you SELECTto know whether you should INSERTor UPDATE, for example:

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 comment: wonderful point! With PostgreSQL's default transaction isolation level ( READ COMMITTED), it is possible that another process inserts (and commits) the "your" part after yours SELECT, but before yours INSERT.

, , - ( , ). SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. . .

Answer 2

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

Answer 3

: , . , , , , . SELECT EXISTS(SELECT 1 ... FROM ... WHERE ...).

, . , , .