Wheeeeee PostgreSQL gets UPSERT-like support!
Posted: Thu May 07, 2015 11:15 pmTHIS IS SUPER EXCITING (HN discussion)!
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!
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!