Thursday, January 7, 2016

PostgreSQL 9.5 fills in lots of gaps

The two big features from my point of view in the long-awaited PostgreSQL 9.5, officially released today, are the INSERT ... ON CONFLICT DO NOTHING / UPDATE feature, and addition of CUBE, ROLLUP and more generalized GROUPING SETS. These are two very important developments that go a long way to filling in the gaps in our feature set.

My contribution has been a bit more more modest than previously for this release. Here are the things I was involved with, along with others:
  • Add jsonb functions jsonb_set() and jsonb_pretty()
  • Allow text, text array, and integer values to be subtracted from jsonb documents
  • Add jsonb || operator
  • Add json_strip_nulls() and jsonb_strip_nulls() functions to remove JSON null values from documents
  • Add jsonb generator functions to_jsonb(), jsonb_object(), jsonb_build_object(), jsonb_build_array(), jsonb_agg(), and jsonb_object_agg()
  • Add \pset option pager_min_lines to control pager invocation in psql
  • Improve psql's line counting used when deciding to invoke the pager
  • Make psql's \sf and \ef commands honor ECHO_HIDDEN
  • Add statistics for minimum, maximum, mean, and standard deviation times to pg_stat_statements
Original work for the first three of these was done by Dmitry Dolgov, and for the last by Mitsumasa Kondo.

This is the fourth straight release where I've done a substantial amount of work on JSON features, and it will be the last for a while at least.  We've come a long way since Robert Haas and I snuck some JSON stuff into release 9.2 at the very last minute. Despite a somewhat rocky road, I venture to say that if we hadn't done that we would not have made as much progress as we have since then.