PostgreSQL keeps on adding new great stuff. It's hard to remember all the good stuff that has been added over the years.
One of the neat ones from the past is the variadic Unnest function which I believe was introduced in PostgreSQL 9.4. It's rare that I ever had to use it, but today I was handed some data where this function was just literally what the doctor ordered. I can't do anything this sweet in other databases I have used.
PostGIS 2.5.1 was released on November 18th 2018 and I finished off packaging the PostGIS 2.5.1 windows builds and installers targeted for PostgreSQL EDB distribution this weekend and pushing them up to stackbuilder. This covers PostgreSQL 9.4-11 64-bit and PostgreSQL 95-10 (32bit).
Note that PostGIS 2.5 series will be the last of the PostGIS 2s. Goodbye PostGIS 2.* and start playing with the in-development version of PostGIS 3. Snapshot binaries for PostGIS 3.0 windows development are also available on the PostGIS windows download page. These should work for both BigSQL and EDB distributions.
Most of our use-cases for the built-in json support in PostgreSQL is not to implement schemaless design storage, but instead to remold data.
One of the common cases we use json support is what we call UNPIVOTING data.
We demonstrated this in Postgres Vision 2018 presentation in slide 23.
This trick won't work in other relational databases that support JSON because
it also uses a long existing feature of PostgreSQL to be able to treat a row as a data field.
Now that we are in midst of moving our databases and client databases to PostgreSQL 10, we started to build our favorite extensions. One popular one which several have asked when we'll have ready for PostgreSQL 10, is the plv8 extensions.
We now have version 1.4.10 for PostgreSQL 10 windows both the 32-bit and 64-bit. The 64-bit versions will work for PostgreSQL EDB Windows as well as the BigSQL distribution.
The JSQuery extension is a PostgreSQL extension developed by Postgres Professional. You can get the source code and instructions for use at
https://github.com/postgrespro/jsquery. JSQuery is a fairly easy compile install if you are on a Nix system.
It provides more query functionality and additional index operator classes to support for JSONB than you get in built in PostgreSQL.
It is supported for PostgreSQL 9.4 and above.
We've built windows binaries for PostgreSQL 64-bit 9.4, 9.5, 9.6, and 10beta1. The 9.4 64-bit will only install on the EDB PostgreSQL 9.4 64-bit distribution.
The 9.5 and 9.6 are compatible with both PostgreSQL EDB and BigSQL distributions. The 10 has only been tested on BigSQL, but should work on EDB when it comes out. We should have 32-bit versions later and will link to those here.
One of the changes coming in PostgreSQL 10 is the ability for the CASE .. WHEN
statement to return multiple rows if the expression contains a set returning function. To demonstrate the feature,
we'll use the classic generate_series function:
Since this article was written, this feature has been removed, because it caused old logic that had sets from doing something different without warning. Refer to this discussion for details.