4 ms·

Really? Why is that a primary show stopper when there are numerous solutions to it that live on the database side as a stored procedure?

by gaadd33 13y ago

burntsushi 13y ago
I think gleenn is talking about the abstraction known as "upsert". Namely, there exists some syntax for expressing "either insert if it doesn't exist or update if it does exist; determine existence by primary key." This process can be achieved in Postgres, but I'm not aware of any simple abstraction for it. But I could be wrong. Does there exist a stored procedure that can be generalized to the point of upsert abstractions in other relational databases? (In this thread, I see a stored procedure for a specific table/set of columns.)
gleenn 13y ago
There might be stored procedures etc, but not being an expert means I'd be concerned about performance and correctness. And the point is especially for perf because that's exactly when I want upsert: to save a lot of back and forth when inputting a lot of data. Also, not being standard means things like bulk upload Ruby gems not supporting faster methods of upload because they can't depend on stored procedures in any easy way that I can tell.
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 ago
    Counters 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.

Really? Why is that a primary show stopper when there are numerous solutions to it that live on the database side as a stored procedure?

by gaadd33 13y ago

Really? Why is that a primary show stopper when there are numerous solutions to it that live on the database side as a stored procedure?

burntsushi 13y ago

I think gleenn is talking about the abstraction known as "upsert". Namely, there exists some syntax for expressing "either insert if it doesn't exist or update if it does exist; determine existence by primary key." This process can be achieved in Postgres, but I'm not aware of any simple abstraction for it. But I could be wrong. Does there exist a stored procedure that can be generalized to the point of upsert abstractions in other relational databases? (In this thread, I see a stored procedure for a specific table/set of columns.)

gleenn 13y ago

There might be stored procedures etc, but not being an expert means I'd be concerned about performance and correctness. And the point is especially for perf because that's exactly when I want upsert: to save a lot of back and forth when inputting a lot of data. Also, not being standard means things like bulk upload Ruby gems not supporting faster methods of upload because they can't depend on stored procedures in any easy way that I can tell.

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 ago
  Counters 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.