3 ms·
The biggest problem with PostgreSQL is the lack of a REPLACE statement and in general first-class "upsert" support. This is the most fundamental mutation opera
by baubu 14y ago
The biggest problem with PostgreSQL is the lack of a REPLACE statement and in general first-class "upsert" support.
This is the most fundamental mutation operation on dictionary, in all programming languages, denoted by:
map[key] = value
It's truly mystifying how something so basic could be missing from something that is supposed to be a better system for storing data.
It also doesn't even fully implement the SQL standards, missing for example the MERGE statement in SQL 2003 standard, which is also utterly baffling.
Note that all databases have catastrophic issues like these, it's just ridiculous how they can be in this terrible state.
- killedbydeath 14y agoI also miss this feature however there are options: a) for small and mid-sized tables you can use a SELECT + conditional INSERT or UPDATE or INSERT-on-exception-UPDATE works b) write-heavy tables are worth being rearchitected to be INSERT-only as this helps avoiding any locks
- sheraz 14y agoIt does take some extra work, but I've implemented this using trigger functions. There is questions and various ways to solve it on stackoverflow [1]: [1] - http://stackoverflow.com/q/1109061/1646663 http://stackoverflow.com/q/1109061/1646663
- jacques_chester 14y agoI like MERGE too, in the proper place. I'm not sure that the absence of MERGE constitutes "this terrible state". It would be like a commercial RDBMS lacking boolean and serial types, necessitating reams and reams of repetitive, error-prone boilerplate constraints and insert/update triggers.
- mercurial 14y agoYeah, but boy, they're sure going to bill you for it.
- malkia 14y agoMaybe because there is no optimal way to do this without locking.
- psadauskas 14y agoYou can do upserts as a single atomic statement using the WITH statement: https://gist.github.com/paul/855efdecaaa2ec4deec7 https://gist.github.com/paul/855efdecaaa2ec4deec7 You can also use it to perform a "find or insert" as a single atomic action: https://gist.github.com/paul/75ec84d131e36492b17b https://gist.github.com/paul/75ec84d131e36492b17b