With the new PostgreSQL version 9.5 about to be released, we take a look at the most anticipated upgrades it will bring, beginning with its new Upsert feature.

PostgreSQL is the open source database system that was originally created at the University of California, Berkeley. It is now over a year since its last decimal point upgrade, see PostgreSQL 9.4 Released, and there are some notable enhancements.

Upsert is the shortened form of 'INSERT ... ON CONFLICT DO NOTHING/UPDATE'  which is activated when the SQL statement has the added in ON CONFLICT DO UPDATE SET clause. It is useful when trying to insert a record and that insertion violates a constraint, instead of resulting in an error message as well as a potential rollback, you now get the chance to either do nothing/ignore it and move on,or  update one or more columns of the row in question

For example , given the row:

and trying to insert a row of:

having specified the: 
ON CONFLICT (id) DO UPDATE SET phone=excluded.phone

clause, instead of the engine just complaining it will update the phone column with the new data. 

Pivoting gets simplified through specifying the grouping type of a typical GROUP BY clause with the introduction of the grouping sets,cube and rollup keywords.

For example,for a given GROUP BY clause on multiple columns,let's say GROUP BY (A,B,C),using the 'Grouping sets' keyword will yield the number of occurrences each  key was found in the dataset. So in this instance it will yield the  count of A,count of B,count of C   

The Cube keyword will yield the occurrences of each combination, that is count of (A,B),count of (A,C), and count of (A,B,C),while Rollup will group the columns in the designated sequence producing the count of (A,B), count of (A,B,C) but not (A,C).

As with the previous release there is improved JSON support. PostgreSQL supports two json type formats, json and jsonb, the latter being recommended as being more efficient: jsonb it can be indexed, whereas plain json cannot. The main differences between the two formats are that jsonb disregards all white space and does not keep track of the order of the keys when storing them, again for matters of efficiency.

Until recently you could just store and extract but not modify jsonb data. This has been remedied by introducing modification operators that allow functionality such as removing keys or array elements from a jsonb object, overwrite existing values or update them.

Also the jsonb_pretty option will prove handy as it can format a long jsonb string for easier reading on the psql command line.Thus a string like:

{"name": "James", "contact": {"phone": "01234 567890", 
   "fax": "07900 112233"}}'::jsonb

will be formatted into:

---------------------------------
  {                              +
      "name": "James",           +
      "contact": {               +
          "fax": "01987 543210", +
          "phone": "07900 112233"+
      }                          +
  }

PostgreSQL has the concept of a foreign database, used for ETL operations where you need to import either the whole schema from the foreign database to the production one, or even just import individual tables. A new Import Foreign Schema syntax can squash this : 

CREATE FOREIGN TABLE remote.customers (
     id int NOT NULL,
     name text,
     company text,
     registered_date date,
     expiry_date date,
     active boolean,
     status text,
     account_level text) SERVER dest_server 
      OPTIONS (schema_name 'public');

into this:

IMPORT FOREIGN SCHEMA public
   FROM SERVER dest_server INTO remote;

When wanting to synchronize your databases for replication purposes, pg_rewind proves very efficient as you now do not need to copy the full back up to the replicated database but instead copy just the pages that have been modified by looking into the Write Ahead Log . Saves a lot of time and sanity.

SKIP LOCKED is useful when needing to get hold of some rows for updating but part of them are locked by another transaction, so this option allows you to skip those rows and perform the update on the rest.

Other new features include Commit timestamp tracking, Sorting optimization (Abbreviated Keys) and much more

It is certain that these new featuress will please existing users. They should also encourage users of other products to convert to PostgreSQL.

More Information

What's new in PostgreSQL 9.5

PostgreSQL

Related Articles

PostgreSQL 9.4 Released

PostgreSQL Plus Cloud Database

To be informed about new articles on I Programmer, sign up for our weekly newsletter,subscribe to the RSS feed and follow us on, Twitter, Facebook, Google+ or Linkedin. 

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info