5 ms·
If you're inserting a lot of data with a high likelihood of conflicts you're probably better off using `INSERT ... SELECT .. WHERE NOT EXISTS` instead, that way
by SPBS 4y ago
If you're inserting a lot of data with a high likelihood of conflicts you're probably better off using `INSERT ... SELECT .. WHERE NOT EXISTS` instead, that way you can have gapless sequences.
-- Will leave gaps
INSERT INTO t (val) VALUES ('abc'), ('def') ON CONFLICT DO NOTHING;
-- Won't leave gaps
INSERT INTO t (val)
SELECT * FROM (VALUES ('abc'), ('def')) AS tmp (val)
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.val = tmp.val);
https://www.db-fiddle.com/f/6s4uYA987owJxu3CEvfu8t/0 https://www.db-fiddle.com/f/6s4uYA987owJxu3CEvfu8t/0
- richbell 4y agoIt's been a while but I'm pretty sure that's exactly what we ended up doing. The advisory lock was because downstream logic required returning the primary key — basically https://en.m.wikipedia.org/wiki/Double-checked_locking https://en.m.wikipedia.org/wiki/Double-checked_locking. Thanks for sharing.