3 ms·
It's annoying that databases don't implement UPSERT natively. As far as I can tell the solution provided by every db requires the repetition of all the damn fie
by bosswipe 6y ago
It's annoying that databases don't implement UPSERT natively. As far as I can tell the solution provided by every db requires the repetition of all the damn fields. To pick on the supposedly more ergonomic postgres method, here's a typical example,
INSERT INTO students(id, firstname, lastname, gender, d_o_b, email)
VALUES (1516, 'Gerard', 'Woodka', 'M', 'January 27 1995', 'gerardo_woodka@gmail.com')
ON CONFLICT (id) DO UPDATE SET
firstname = EXCLUDED.firstname,
lastname = EXCLUDED.lastname,
gender = EXCLUDED.gender,
d_o_b = EXCLUDED.d_o_b,
email = EXCLUDED.email;
At least it doesn't force you to repeat the values like many other servers. But still the repetition is bug prone and hard to maintain as the table changes. And it's just annoying to type.
- nkozyra 6y agoI think you really want to repeat the fields because inserting versus updating could require dramatically different behavior.
- masklinn 6y agoThere are many cases where you only want to update a subset of the fields, or even not update anything, but it's also true that a shortcut to update everything but the conflicting fields would be nice.
- nkozyra 6y agoOh I get the convenience, it just seems like an easy way to shoot yourself in the foot, like an update without a limit clause.
- ufmace 6y agoI appreciate that it's a little annoying and repetitive, but I think in this case, it really is better, because the business logic for exactly what should be done with each value in the case of a conflict can vary so widely. It's good to have the full flexibility of SQL and all of the associated values to work with for the purpose of constructing the new value for the column.