On: Postgres Demystified

[baubu]

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.

[psadauskas → baubu]

You can do upserts as a single atomic statement using the WITH statement:

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

[malkia → baubu]

Maybe because there is no optimal way to do this without locking.

[killedbydeath → baubu]

I 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

[jacques_chester → baubu]

I 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 → jacques_chester]

Yeah, but boy, they're sure going to bill you for it.

[sheraz → baubu]

It 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