PostgreSQL Insert into table A and table B if table A does not contain the value

Question

Hi I am trying to do the following in a postgreSql table but am having trouble with the syntax.

Psuedocode:

if (tableA.column1 does not contain value1)
{
    INSERT INTO tableA (column1, column2)
    VALUES value1, value2
}

// do this check even if the above already existed
if (tableB does not contain value1 and value3 in the same row)
{
    // it is ok if value1 and value3 already exist in the table, just not the same row
    INSERT INTO tableB (column1, column2)
    VALUES (value1, value3)
}

return true

Any help with the actual syntax for this operation would be much appreciated!

I don't see how the first and the second condition "belong" together. You can simply run both independently from each other. — user330315
– user330315, Commented Mar 22, 2013 at 14:12

user330315 · Accepted Answer · 2013-03-22 14:14:23Z

2

-- first insert:
insert into tablea (col1, col2)
select 1,2
from tablea
where not exists (select * from tablea where col1 = 1);

-- second insert (same logic to apply a conditional insert)
insert into tableb (col1, col2)
select 1,2
from tableb
where not exists (select * from tableb where col1 = 1 and col2 = 2);

answered Mar 22, 2013 at 14:14

user330315

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

3 Comments

Craig Ringer Over a year ago

Just remember to LOCK the tables or be prepared to handle errors (if unique) or duplicates (if not unique) due to multiple transactions concurrently checking and inserting the same record.

mvp Over a year ago

@CraigRinger: why locking? Isn't simply wrapping into one transaction enough?

Craig Ringer Over a year ago

@mvp If you know nobody else will be concurrently inserting, yes. But if you know that you know it won't cost you anything to lock either. Inserts don't block each other. If there's a unique key shared between two concurrent inserts then both will SELECT and see the key doesn't exist then both will try to insert it. One will win, the other will fail to commit with a duplicate key error despite not being able to see the conflicting row. If there isn't a unique key then visibility rules will result in neither seeing the row the other is inserting and two rows getting inserted.

Collectives™ on Stack Overflow

PostgreSQL Insert into table A and table B if table A does not contain the value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related