Non-overlap, continuous timestamp ranges (tstzrang

问题:

CREATE TABLE operating_period (
  id SERIAL NOT NULL PRIMARY KEY,
  during TSTZRANGE NOT NULL,
  -- other meta fields
);

Requirements: 1. No operating period can overlap with each other

Question:

1. How do I add a constraint to make sure that there is no overlap in the operating hours?
2. In terms of query speed, am I better off with two columns (start_at, end_at) or is GIST index fast for tstzrange?
3. In schema design, is tstzrange commonly used? Or am I better of with two columns?

回答1:

The answer to 1. is clear. To make sure there is no overlap use an exclusion constraint:

CREATE TABLE operating_period (
  id serial PRIMARY KEY                -- PK is NOT NULL automatically
 ,during tstzrange NOT NULL
 ,EXCLUDE USING gist (during WITH &&)  -- no overlap
);

This is implemented with a GiST index on during, that supports many types of queries automatically. Related answer:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
• Perform this hours of operation query in PostgreSQL

Answers to 2. and 3. are not as clear, because it really depends on a lot of things. Both have their pros and cons. For opening hours I would most likely go with range types in current versions of Postgres. I would also enforce [) bounds for all entries to keep things simple. Details in the first linked answer.

If you should go with (start_at, end_at), you'll be interested in the OVERLAPS operator:

• Getting results between two dates in PostgreSQL
• Find overlapping date ranges in PostgreSQL

_{Either way, the guideline here is to ask one question per question, not a whole list ...}