特定
=> select * from referenced;
referenced_id | name
---------------+-------
1 | one
2 | two
3 | three
和
=> select * from entries;
entry_id | referenced_id | name
----------+---------------+------------------
1 | 3 | references three
其中referenced_id和entry_id是主键.
如果entry_id已存在或引用的项不存在,我想要一个跳过插入的条目的insert语句.第一个很容易做到：
INSERT INTO entries VALUES (1,2,'references two') ON CONFLICT (entry_id) DO NOTHING;
是否有可能在这里检查是否存在外键？
解决方法
是的,将输入行连接到引用的表,从而删除FK列上没有匹配的行：
INSERT INTO entries(entry_id,referenced_id,name)
SELECT val.entry_id,val.referenced_id,val.name
FROM (
VALUES (1,'references two')
-- more?
) val (entry_id,name)
JOIN referenced USING (referenced_id) -- drop rows without matching FK
ON CONFLICT (entry_id) DO NOTHING; -- drop rows with duplicate id
UPSERT本身(INSERT …… ON CONFLICT DO NOTHING)仅对唯一违规做出反应. The manual:
ON CONFLICTcan be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See 07001 below.)
