Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Question

I am creating a database which stores arbitrary date/time ranges in PostgreSQL 9.2.4. I want to place a constraint on this database which forces the date/time ranges to be non-overlapping, and non-adjacent (since two adjacent ranges can be expressed as a single continuous range).

To do this, I am using an EXCLUDE constraint with a GiST index. Here is the constraint I have currently:

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 columns from and to are both TIMESTAMP WITHOUT TIME ZONE, and are date/times stored in UTC (I convert to UTC before inserting data into these columns in my application, and I have my database's timezone set to "UTC" in postgresql.conf).

The problem I am thinking I might have, though, is that this constraint is making the (incorrect) assumption that there are no time increments smaller than one second.

It is worth noting that, for the particular data I am storing, I only need second resolution. However, I feel that I may still need to deal with this since the SQL types timestamp and timestamptz are both higher resolution than one second.

My question is either: is there any problem with simply assuming second resolution, since that's all my application needs (or wants), or, if there is, how can I alter this constraint to deal with fractions-of-a-second in a robust way?

Sellingplater

Answer 1

Range types consist of lower and upper bound, and each can be included or excluded.
The canonical form (and default for range types) is to include the lower and exclude the upper bound.

Inclusive bounds '[]'

You could include lower and upper bound ([]), and enforce it with a CHECK constraint using range functions.

Then "adjacent" ranges overlap. Excluding overlapping ranges seems clear. There is a code example in the manual.

CREATE TABLE tbl (
   tbl_id serial PRIMARY KEY
 , tsr tsrange
 , CONSTRAINT tsr_no_overlap EXCLUDE USING gist (tsr WITH &&)
 , CONSTRAINT tsr_enforce_incl_bounds CHECK (lower_inc(tsr) AND upper_inc(tsr))  -- all bounds inclusive!
);

Only ranges with inclusive bounds are allowed:

INSERT INTO tbl(tsr) VALUES ('[2013-10-22 00:00, 2013-10-22 01:00]');

db<>fiddle here

Canonical bounds '[)'

Enforce [) bounds (including lower and excluding upper).

In addition, create another exclusion constraint employing the adjacent operator -|- to also exclude adjacent entries. Both are based on GiST indexes as GIN is currently not supported for this.

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 sqlfiddle}

Unfortunately, this creates two identical GiST indexes to implement both exclusion constraints, where one would suffice, logically.

Polariscope

This solution is going to work really well. Although I could have faked it using separate columns, switching to tsrange makes this trivial, and gives me all of the other range operators, which are going to come in very handy for using / managing this data. Thanks! :) – Sellingplater

Would this be useful if I want to use ON CONFLICT clause when inserting an adjacent tsr value? I want to know if ON CONFLICT(tsr) will trigger if an adjacent tsr value is inserted. – Tiannatiara

@madtyn: Yes, the ON CONFLICT clause covers conflicts with EXCLUSION constraints, but only for DO NOTHING. The manual: Note that exclusion constraints are not supported as arbiters with ON CONFLICT DO UPDATE. Consider demos in the updated db<>fiddle I added above. – Polariscope

Answer 2

You can rewrite the exclude with the range type introduced in 9.2. Better yet, you could replace the two fields with a range. See "Constraints on Ranges" here, with an example that basically amounts to your use case:

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

Biparietal

The problem is, in addition to a non-overlapping constraint, I also want to prevent adjacent entries. If we let unit be the smallest unit of time these types will store, for any two entries fromA - toA and fromB - toB, if toA + unit = fromB (or vice-versa), then they can be represented as a single entry of fromA - toB. The problem I have is that I don't know what unit is, or if it's even defined, so I'm currently using a value of 1 (second). – Sellingplater

unit is a fraction of a microsecond, unless you're using a timestamp(0) to force it to a second with occasional rounding problems that make you run into the exclude constraint. For the adjacent constraint, I'd wager you can exclude an expression, e.g. ((during + interval '1 second') with &&). – Biparietal

Answer 3

The problem I am thinking I might have, though, is that this constraint is making the (incorrect) assumption that there are no time increments smaller than one second.

You're OK there, consider:

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

Zr