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".

Answer 1

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.