Postgres gist constraint difference between tsrange datatype and function

Question

For my database design, I have to options for storing timestamp ranges and preventing overlaps using a gist exclude constraint:

Option 1

create table example (
  id bigserial primary key,
  kind_id bigint not null,
  start timestamp not null,
  stop timestamp,
  constraint test_start_stop exclude using gist (kind_id with =, tsrange(start, stop) with &&)
);

Option 2

create table example (
  id bigserial primary key,
  kind_id bigint not null,
  start_stop tsrange not null,
  constraint test_start_stop exclude using gist (kind_id with =, start_stop with &&)
);

When looking at the documentation, there does not seem to be an implementation for Postgres's tsrange or tstzrange datatypes for the FromSql and ToSql traits in postgres-rs.

These types are supported in sqlx-rs but I'm reluctant to switch because of the work that goes into refactoring.

Are there any drawbacks that I should be aware of when using the first option ?

Addendum: in this thread Erwin Brandstetter cites a line from the manual "Note that exclusion constraints are not supported as arbiters with ON CONFLICT DO UPDATE.", and he mentions that "the ON CONFLICT clause covers conflicts with EXCLUSION constraints, but only for DO NOTHING".

Code4R7 · Accepted Answer · 2025-08-16 11:42:46Z

It depends

Ranged types:

are semantically correct, two values form an actual range
can be empty in which case boundary data is lost, zero length ranges can not be stored
offer range operators which can make life easier
boundaries can be inclusive, exclusive or unbounded (null) at both ends, which can require more work in constraints if you want to disallow null
associated range functions are counter-intuitive, for example lower_inf() does not test for 'infinity', it tests for null. And lower_inc('[,]'::daterange) returns false
offer range operators are counter-intuitive, for example <@ will return true even with boundaries will null values
are less compatible with other DBMS-es
have no significant performance difference compared to their non-ranged counterparts
require one less column per range
can be used for foreign key references to enforce range boundaries on another table, like their counterparts
allow -infinity and infinity, just like non-ranged values
allow null values as boundaries, so if you want to disallow null (next to not null on the column) you need extra constraints

Read the documentation entirely beforehand, and then make your choice. It is a matter of taste.

Collectives™ on Stack Overflow

Postgres gist constraint difference between tsrange datatype and function

1 Answer 1

It depends

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

It depends

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related