It is currently Sat Jan 23, 2021 3:30 am

Wheeeeee PostgreSQL gets UPSERT-like support!

If you have something technology-related to share and don't feel like cluttering up General Chat, post it here. Anything is fair game and anything highly technical is preferred.

Wheeeeee PostgreSQL gets UPSERT-like support!

Postby Zancarius » Thu May 07, 2015 11:15 pm


Some of you may or may not deal with databases on a regular basis, but this is insaaaaaaanely useful for web applications where an extra round-trip to the database increases response latency. As it stands, the only way to achieve this in Postgres thusfar has been to rely on some manner of magical stored procedures which can be a bit unruly to maintain.

UPSERT (portmanteau of "update" and "insert") reduces application workload somewhat but it's best illustrated, perhaps somewhat ironically, through MySQL's syntax of INSERT OR REPLACE. Essentially, when a conflict occurs during an insert (which creates a record), the upsert will instead update the affected rows. It's not a tool I've used with a great deal of regularity, but when you need it, it's extremely useful. Otherwise, short of stored procedures, you're left with querying the database for an existing row and then deciding programmatically to create it if it doesn't exist or create a separate query to update it if it does. Two queries means two complete query/response cycles to the database and more time consumed by your application to dispatch the final response to the user.

Postgres is really doing some neat stuff these days. It's a bit of a shame Oracle has let MySQL stagnate, but there are a few forks at least that are continuing with incremental improvements (Percona, MariaDB). Though I can't say I'd trust anything other than Postgres at a bare minimum these days...

Fun times ahead!
I gave that lich a phylactery shard. Liches love phylactery shards.
User avatar
Site Admin
Posts: 3907
Joined: Wed Jul 05, 2006 3:06 pm
Location: New Mexico
Gender: Male

Return to Technology Lounge

Who is online

Users browsing this forum: No registered users and 1 guest