Postgres: WHERE... AND syntax problem

Question

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?

Mark Byers · Accepted Answer · 2010-12-29 14:54:22Z

3

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.

edited Dec 29, 2010 at 14:54

answered Dec 29, 2010 at 14:31

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

Frank Heikens Over a year ago

There is big difference between the condition in the SELECT and a UNIQUE constraint. The condition doesn't provide any uniqueness at all, different transactions can insert the same data at the same moment.

Frank Heikens · Accepted Answer · 2010-12-29 14:46:27Z

0

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.

answered Dec 29, 2010 at 14:46

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

Postgres: WHERE... AND syntax problem

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related