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.
For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension v1.2.2 release for PostgreSQL 10, 9.6, 9.5, and 9.4 Windows 64-bit.
These should work fine on both PostgreSQL EDB windows distributions as well as the BigSQL windows 64 distributions.
Instructions in the ReadMe.txt for where to copy the files to for each distribution. Caution should be taken especially with BigSQL to not overwrite existing BigSQL files.
Many of the files in this already exist in the BigSQL distribution since we both compile using the Mingw64 chain.
If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.
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.