CODEWITHSUNDEEP
sqlpostgresql
AP257
AP257

Reputation: 93773

Postgres: WHERE... AND syntax problem

Can anyone help with a Postgres syntax problem? I'm trying to insert a record, but before doing so, check it doesn't exist, using WHERE... AND. 

=# \d domes_manor_place;
 id       | integer | not null default nextval('domes_manor_place_id_seq'::regclass)
 manor_id | integer | not null
 place_id | integer | not null
=# select * from domes_manor_place where place_id='13621';                                                                            
 24017 |    22276 |    13621

OK, so we know that there is no record with manor_id=22398 and place_id=13621. Let's try to insert it with our `WHERE... AND' syntax:

=# INSERT INTO domes_manor_place (manor_id, place_id) SELECT 22398, 13621                                                                 
WHERE (22398 NOT IN (SELECT manor_id FROM domes_manor_place)) AND                                                                             
(13621 NOT IN (SELECT place_id FROM domes_manor_place));
INSERT 0 0

It won't insert the record - so what's wrong with my syntax?

Upvotes: 1

Views: 2140

Answers (2)

Mark Byers
Mark Byers

Reputation: 837966

Try this:

WHERE (22398, 13621) NOT IN (SELECT manor_id, place_id FROM domes_manor_place)

By the way, a much better approach is to use a unique constraint on the pair of columns. This will cause the insert to fail if a row already exists.

Upvotes: 3

Frank Heikens
Frank Heikens

Reputation: 126992

You need a UNIQUE-constraint, the SELECT can't help because it can't see data that is not committed yet. Different transactions can insert new records at the same moment and these are all "unique"... NOT.

Upvotes: 0

Related Questions