Postgres：WHERE...AND 语法问题
任何人都可以帮助解决 Postgres 语法问题吗？我正在尝试插入一条记录，但在此之前，请使用 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
好的，我们知道没有 manor_id=22398 和 place_id=13621 的记录。让我们尝试使用“WHERE... AND”语法插入它：
=# 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
它不会插入记录 - 那么我的语法有什么问题？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
试试这个：
顺便说一句，更好的方法是对这对列使用唯一约束。如果行已存在，这将导致插入失败。
Try this:
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.
您需要一个 UNIQUE 约束，SELECT 没有帮助，因为它看不到尚未提交的数据。不同的事务可以在同一时刻插入新记录，并且这些记录都是“唯一的”......不是。
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.