Table Of Contents

Today is April 1st. Having no thoughts on Fools jokes for today, I dug up one of our old April fools, and it was pretty scary how the joke is just about true now. Yes SQL Server now really does run on Linux and is on it's 2017th edition, but still a poor competition to PostgreSQL.

A goody from our old joke archives

One of the features we are most excited about in recently released PostgreSQL 14 is the introduction of Multirange types. In a nutshell Multirange types are sets of non-overlapping ranges. Unlike array of ranges, they prevent overlaps and thus allow you to effectively model ranges with gaps.

One of the use cases we have for them is modeling time. For example if you wanted to keep track of the cumulative periods and how many days someone is in a hospital, you could store this as a datemultirange type.

There are quite a few operators and functions available in PostgreSQL 14, but some glaring ones we'd need include aggregates such as a union aggregate. That currently doesn't exist. There are however your standard operators like + (union two ranges) and * for intersection, and - for difference as well as your common containment boolean operators.

Defining a multirange variable

The canonical form of a multirange type is composed of an outer {} followed by a comma separated list of ranges.

SELECT '{[2021-05-01, 2021-06-01), [2020-09-01, 2020-10-01)
	, [2021-09-01, 2021-09-13)}'::datemultirange;

outputs:

Observe how it changed the sorting to chronological order.

Multiranges can't have overlapping ranges, but YOU CAN cast an overlapping range set into a multirange without getting an error. Let's see what happens here:

SELECT '{[2021-05-01, 2021-06-01), [2021-09-02, 2021-09-15)
	, [2021-09-01, 2021-09-13)}'::datemultirange;

outputs:

Observe how it collapsed the last two date ranges into one that contains the union of them.

Using multirange types

Imagine you had a regular table that just tracks stays for patients and the period of time of the stay (as a daterange), one record per stay. We don't care if stays overlap because they might represent going in for a particular procedure one day, going back same day for another procedure and so forth. If it's on the same day the two sets together for some of our calcs would be treated as one day, but you still need to keep the reason information for billing.

Your table would look something like this:

CREATE TABLE stays(id bigint GENERATED ALWAYS AS IDENTITY, 
	id_patient bigint, 
  period_stay daterange, reason text,
  CONSTRAINT pk_stays PRIMARY KEY (id) );

INSERT INTO stays(id_patient, period_stay, reason)
VALUES (1, daterange('2021-05-10', '2021-06-01'), 'Operation and healing' ),
	(2, daterange('2021-05-12', '2021-05-13'), 'X-Ray' ),
	(2, daterange('2021-05-13', '2021-05-14'), 'Blood' ),
	(2, daterange('2021-05-13', '2021-05-14'), 'MRI' ),
	(2, daterange('2021-06-13', '2021-06-14'), 'Spinal Tap' );

If you wanted to create a query that aggregates these into a single record per patient and period of stay is represented as a multirange, then you can use the range_agg function, which returns a multirange.

This example demonstrates how to do it using array_agg to aggregate the ranges into an array and then using the canonical text form to cast to datemultirange.

SELECT id_patient, 
	range_agg(period_stay) AS period_total_stay
FROM stays
GROUP BY id_patient
ORDER BY id_patient;

The output of the above query looks like this:

 id_patient |                 period_total_stay
------------+---------------------------------------------------
          1 | {[2021-05-10,2021-06-01)}
          2 | {[2021-05-12,2021-05-14),[2021-06-13,2021-06-14)}

One of our favorite functions is unnest and unnest is supported for multiranges and returns back a set of ranges. With the combination of range_agg and unnest you can create a set of non-overlapping ranges from a set of overlapping ones as follows:

SELECT id_patient, 
	unnest(range_agg(period_stay)) AS period_deduped_stay
FROM stays
GROUP BY id_patient
ORDER BY id_patient;

yields:

id_patient |   period_deduped_stay
------------+-------------------------
          1 | [2021-05-10,2021-06-01)
          2 | [2021-05-12,2021-05-14)
          2 | [2021-06-13,2021-06-14)
(3 rows)

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 for PostgreSQL Windows.

These are designed to work with PostgreSQL EDB windows distributions.

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.

http extension binaries for PostgreSQL 17, 16, 15, 14, 13, 12 11, 10, 9.6, 9.5, and 9.4 windows 64-bit downloads

PostgreSQL 10-14 64-bit are 1.5.0, other versions are older http versions.

The curl library for http is built with SSL support and utilizes the ssleasy.dll packaged with the EDB installs.

• PostgreSQL 17 w64 http (zip) 7z version
• PostgreSQL 16 w64 http (zip) 7z version
• PostgreSQL 15 w64 http (zip) 7z version
• PostgreSQL 14 w64 http (zip) 7z version
• PostgreSQL 13 w64 http (zip) 7z version
• PostgreSQL 12 w64 http (zip) 7z version
• PostgreSQL 11 w64 http (zip) 7z version
• PostgreSQL 10 w64 http (zip) 7z version
• PostgreSQL 9.6 w64 http (zip) 7z version
• PostgreSQL 9.5 w64 http (zip) 7z version
• PostgreSQL 9.4 w64 http (zip) 7z version

http extension binaries for PostgreSQL 10, 9.6, 9.5, and 9.4 windows 32-bit downloads

• PostgreSQL 10 w32 http (zip) 7z version
• PostgreSQL 9.6 w32 http (zip) 7z version
• PostgreSQL 9.5 w32 http (zip) 7z version
• PostgreSQL 9.4 w32 http (zip) 7z version

http quick primer

To enable in a database after having installed the binaries.

CREATE EXTENSION http;

Do a basic get

SELECT h.content, h.content_type, hkv.value As dt
FROM http_get('http://postgis.net/tips/') AS h 
    LEFT JOIN LATERAL (SELECT *  
    FROM unnest(h.headers) 
        WHERE field =  'Date') AS hkv ON true;

Check out more examples at: https://github.com/pramsey/pgsql-http

We recently installed PostgreSQL 11 on an Ubuntu 18.04 using apt.postgresql.org. Many of our favorite extensions were already available via apt (postgis, ogr_fdw to name a few), but it didn't have the http extension we use a lot. The http extension is pretty handy for querying things like Salesforce and other web api based systems. We'll outline the basic compile and install steps. While it's specific to the http extension, the process is similar for any other extension you may need to compile.

For this exercise, I'm going to assume you have a working PostgreSQL install you got from apt.postgresql.org. If you don't you can check out PostGIS 2.4 and PostgreSQL 10 install on Ubuntu which covers installing PostgreSQL, PostGIS, and pgRouting on Ubuntu.

Building extensions general requirements

For any extension written in C or C++, You'll need a couple extra things, you may not have

sudo -i  #get into root mode
apt install make gcc g++
apt install postgresql-server-dev-11

If you are running a different version of PostgreSQL, replace the number 11 with the version you are running.

Building http

The http extension relies on curl development library, so in addition to the aforementioned, you'll need to do:

Now we are ready to build. The latest as of this writing is 1.3.0, you should check if there is newer when you build.

wget https://github.com/pramsey/pgsql-http/archive/v1.3.0.tar.gz
tar xvfz v1.3.0.tar.gz
cd pgsql-http-1.3.0
make && make install

If all is successful you should see something like this:

/usr/bin/install -c -m 644 .//http--1.3.sql .//http--1.2--1.3.sql .//http--1.1--1.2.sql .//http--1.0--1.1.sql  
'/usr/share/postgresql/11/extension/'

Installing http extension in your database

As a general rule we don't like our public schema cluttered with extension functions, so we usually install extensions in contrib schema or a schema dedicated to the extension or related extensions. So our steps below are a few more than a standard vanila install.

su postgres
psql
CREATE DATABASE test_http;
\connect test_http;
CREATE SCHEMA contrib;
GRANT USAGE ON SCHEMA contrib TO some_user_group;
ALTER DATABASE test_http SET search_path=public,contrib;
CREATE EXTENSION http SCHEMA contrib;
\connect test_http -- I do this so search path takes effect
-- test the extension by grabbing first 100 characters of web page
SELECT left(content,100) FROM http_get('https://postgis.net');

You can find more examples of usage at https://github.com/pramsey/pgsql-http

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.

To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

• Windows 64-bit: zip 7z

We are no longer including 32-bit binaries since EDB has stopped supporting 32-bit windows for PostgreSQL 11 and up.

This package contains the following FDWs:

• odbc_fdw (version 0.4.0) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
• file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 2.2) and also BigSQL distributions.

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 11 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 12 Windows 64-bit.

To use these, copy the files into your PostgreSQL 12 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

• Windows 64-bit: zip 7z

We are no longer including 32-bit binaries since EDB has stopped supporting 32-bit windows for PostgreSQL 11 and up.

This package contains the following FDWs:

• odbc_fdw (version 0.4.0) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
• file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 3.0) .

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 12 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 13 Windows 64-bit.

To use these, copy the files into your PostgreSQL 13 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

• Windows 64-bit: packages fdw_win64_13_bin.zip fdw_win64_13_bin.7z

This package contains the following FDWs:

• odbc_fdw (version 0.5.2.3) (source) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
• file_textarray_fdw source, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 3.0) .

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 13 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 14 Windows 64-bit.

To use these, copy the files into your PostgreSQL 14 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

• Windows 64-bit package fdw_win64_14_bin.zip fdw_win64_14_bin.7z

This package contains the following FDWs:

• odbc_fdw (version 0.5.2.3) for connecting to ODBC data sources such as SQL Server, Oracle, MS Access databases, and anything else that has a 64-bit ODBC driver. Note that since this is for PostgreSQL 64-bit, it can only use ODBC 64-bit connections.
• file_textarray_fdw, great and fast for working with arbitrary and pesky delimited data. Especially where they weren't considerate enough to give you the same number of columns per row.

Note this package does not include ogr_fdw since ogr_fdw is packaged as part of PostGIS packages from EnterpriseDb Stackbuilder (for PostGIS >= 3.0) .

If you do not have PostGIS installed (and don't want to for some reason) and want to use ogr_fdw on windows, you can download from: Winnie's PG 14 Extras. ogr_fdw is a great FDW for querying not just spatial data, but also a ton of other file formats or relational (including odbc, dbase files, spreadsheets) since spatial is a superset.