3 ms·
Yes, for me this is a showstopper. All MySQL projects I've worked on have REPLACE INTO and/or INSERT … ON DUPLICATE. Postgres' equivalents of UPSERT that I've
by pornel 13y ago
Yes, for me this is a showstopper. All MySQL projects I've worked on have REPLACE INTO and/or INSERT … ON DUPLICATE.
Postgres' equivalents of UPSERT that I've found either only work correctly with single-row inserts or are inefficient and complicated.
It's really hard to sell Postgresql to developers of a MySQL project when a simple 1-line query explodes into a complicated stored procedure for apparently no good reason.
- __david__ 13y ago> Yes, for me this is a showstopper. All MySQL projects I've worked on have REPLACE INTO and/or INSERT … ON DUPLICATE. Curious. What is your use case? From personal experience, I've used "ON DUPLICATE", but we ended up dumping it and making our API better/more secure. This was even before we switched to Postgres.
- pornel 13y agoCounters where the set of things you count is open-ended (e.g. words in a naive bayes classifier): INSERT INTO word_counts(word) VALUES('hello'),('world') ON DUPLICATE KEY UPDATE cnt = cnt+1; Lists where you track 'last seen': REPLACE INTO last_login(user_id, date) VALUES(123, NOW()); With Postgres' INSERT … RETURNING this would be awesome: INSERT INTO log(date, user_agent_id) VALUES(now(), INSERT IGNORE INTO user_agents VALUES('Mozilla') RETURNING id) This way I could store any long string only once, and do it atomically in one query.