So two design patterns for a makeshift UPSERT have been presented -
one is to check beforehand, and only insert if the item isn't present
already, the other is to do the insert blindly and let PG check for
you, and catch any exceptions.
I'm also wondering what people's ideas are for a sort of BULK
UPSERT. I often find myself inserting the results of a SELECT and
wanting a similar check for already existing rows. The idiom I've
stumbled upon looks like this:
insert into foo (x, y, z)
select a, b, c from bar join bax ...
EXCEPT
select x, y, z from foo;
Namely, I subtract from the results to be inserted any rows that are
already present in the target table. This can actually even be used
for UPSERTing a single row, and has the virtue of being pure SQL, but
I've wondered about its efficiency. One alternative would be to
iterate over the SELECT result with a procedural language, and do a
series of UPSERTS, but that seems unlikely to be as efficient for a
large result set. Any comments about the relative merits of these or
other alternatives?
Thanks.
- John Burger
MITRE