HTTPS SSH

Closed form discrete range types for PostgreSQL

The three discrete builtin range types for PostgreSQL, daterange, int4range and int8range all have canonicalization functions that convert them to the '[)' format, with the lower bound included and the upper bound excluded.

Often it's desirable to have the closed format ([]) instead, where the upper bound is included. The extension provides closed format variants for these three types. The new types are cdaterange, cint4range and cint8range. In all other respects than the canonicalization functions, these types should work the same as the native types.

Note that the two-argument constructor will act as though there were a third argument of '[)'. This is hardcoded into the underlying core postgres function and can't be changed easily. However, the value will still be stored in closed format, i.e. cint4range(1,5) is converted to and stored as [1,4]. It is probably best not to use the two argument constructor function to avoid confusion when using these types.

Building and Installing

With pg_config in the path, do:

make install

Example Use

CREATE EXTENSION cranges;

CREATE TABLE mytable (
    t    text,
    r    cdaterange
);

INSERT INTO mytable
VALUES ('stuff', '[2015-06-11, 2015-06-29]');

SELECT * FROM mytable;

SELECT cdaterange('2015-06-11', '2015-06-29', '[]');