Lead Image © Sergey Nivens, 123RF.com

PostgreSQL 9.5 – What's new; what's better?

An Eye for Details

Article from ADMIN 31/2016

By Peter Eisentraut

As expected, a further release of the PostgreSQL open source database system was launched last fall, offering a number of impressive new features.

PostgreSQL 9.5 contains more than 100 changes, including dozens of performance improvements. The first highlight in PostgreSQL 9.5 is the extension of the INSERT command to include the ON CONFLICT DO UPDATE clause, which lets you stipulate that an UPDATE is performed instead of an INSERT if the line to be inserted already exists. This behavior is also known as "UPSERT."

UPSERT

In practical applications, the UPSERT case occurs quite often, but users of PostgreSQL previously had to implement the solution manually as a stored procedure or rely on the logic in the client application. The difficulty originates with the defaults in the SQL standard and other database systems, which cause considerable technical problems unacceptable to the PostgreSQL developers [1]. Although the logic of an UPSERT is quite simple, it becomes complicated if this logic needs to deliver high performance in parallel operations without generating deadlocks or sporadic, unique constraint errors, which is very much the case with other implementations.

For these reasons, the PostgreSQL project decided specifically to design a separate syntax with its own semantics. In this example, a table stores subscriptions from a service with a user ID, the subscription type, and the start and end dates:

CREATE TABLE subscriptions (
  user_id int NOT NULL,
  subscription_type text NOT NULL,
  started date NOT NULL,
  expires date NOT NULL,
  PRIMARY KEY (user_id, subscription_type)
);

An UPSERT operation is now required to either insert a new entry or update an existing entry. Previously, developers needed to rely on a loop that performed INSERT or UPDATE operations until one of them worked without error. You could do this, for example, with a PL/pgSQL function of

...

Use Express-Checkout link below to read the full article (PDF).