Prevent contiguous / overlapping records with EXCLUDE in PostgreSQL

Question

I am creating a database that stores arbitrary date / time ranges in PostgreSQL 9.2.4. I want to set a restriction on this database, which causes date / time ranges to be non-overlapping and non-contiguous (since two adjacent ranges can be expressed as one continuous range).

For this, I use the EXCLUDE constraint with the GiST index. Here is the limitation that I have:

 ADD CONSTRAINT overlap_exclude EXCLUDE USING GIST ( box( point ( extract(EPOCH FROM "from") - 1, extract(EPOCH FROM "from") - 1 ), point ( extract(EPOCH FROM "to"), extract(EPOCH FROM "to") ) ) WITH && ); 

The from and to columns are TIMESTAMP WITHOUT TIME ZONE and are the date / time stored in UTC (I convert to UTC before inserting data into these columns in my application and I have the time zone of my "UTC" database set to postgresql. conf).

The problem that I think may have, however, is that this restriction makes the (wrong) assumption that the time intervals are less than one second.

It is worth noting that for the specific data that I store, I only need a second resolution. However, I feel that I still need to deal with this, because the SQL types timestamp and timestamptz have a higher resolution than one second.

My question is: is there a problem with simply accepting the second resolution, since my whole application needs (or wants), or, if there is, how can I change this restriction to deal with fractions in a second reliable way?

Answer 1

Range types consist of lower and upper bounds that can be included or excluded . A typical use case (and by default for range types) is to include the lower and exclude the upper bound.

The exclusion of overlapping ranges seems obvious. There is a good code example in the manual.

In addition, create another exception constraint using the adjacent operator -|- to also exclude adjacent entries. Both must be based on GiST indices , since GIN is not currently supported for this.

To keep it clean, I would set the [) borders (including the bottom and excluding the top) for all records with a CHECK constraint using range functions :

 CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , tsr tsrange , CONSTRAINT tsr_no_overlap EXCLUDE USING gist (tsr WITH &&) , CONSTRAINT tsr_no_adjacent EXCLUDE USING gist (tsr WITH -|-) , CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr)) ); 

db <> fiddle here
(Old SQL Fiddle )

_{Unfortunately, this creates two identical GiST indexes to implement both exception restrictions, where one is logically sufficient. This seems like a flaw in the current implementation (at least until Postgres 11).}

Answer 2

You can rewrite the exception with the range type introduced in 9.2. Even better, you could replace the two fields with a range. See Here "Range Restrictions", an example that basically boils down to your use case:

http://www.postgresql.org/docs/current/static/rangetypes.html

Answer 3

The problem that I mean, maybe, is that this restriction makes the (wrong) assumption that there is no time in steps of less than one second.

You are fine, think:

 select extract ('epoch' from now()) , extract ('epoch' from now()::timestamp(0))