Closed form discrete range types for PostgreSQL
The three discrete builtin range types for PostgreSQL,
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
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
'[)'. 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
It is probably best not to use the two argument constructor function to avoid
confusion when using these types.
Building and Installing
pg_config in the path, do:
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', '');