postgresqlrustdatabase-designrange

Postgres gist constraint difference between tsrange datatype and function


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


Solution

  • Always use range types for ranges

    Non-range types maintain compatibility with other database systems at the cost of using functions that would likely increase CPU usage and require more SQL code.

    Non-range types can not handle the values -infinity and infinity, and as a result you can not make use of range operators like <@, when non-range types use null values instead of infinity values. The same is true for on update cascade and on update delete feature on foreign keys, because currently PostgreSQL (16) has not yet implemented match partial for foreign keys.

    Instead, always use range types when you want to make use of them, for example if you want to make sure that date ranges of table b are within the date range of table a:

    create table a (
      id bigserial not null primary key,
      p daterange,
      unique (id, p)
    );
    insert into a(p) values
    ('[2025-01-01, 2025-03-01)'),
    ('[2025-04-01, infinity)');
    
    create table b (
      id bigserial not null primary key,
      a bigint references a(id),
      a_p daterange,
      p daterange,
      foreign key (a, a_p) references a (id, p) on update cascade,
      check (p <@ a_p)
    );
    insert into b (a, a_p, p) values
    (1, '[2025-01-01, 2025-03-01)', '[2025-01-02, 2025-02-28)'),
    (2, '[2025-04-01, infinity)', '[2025-04-02, infinity)');
    

    Here, PostgreSQL will guarantee that the data will be constrained. When you would try to enter invalid data:

    update a set p = '[2025-01-01,2025-02-01)';  -- error from check in table b
    

    PostgreSQL will prevent you from doing so and it will respond with an error:

    ERROR:  new row for relation "b" violates check constraint "b_check"
    DETAIL:  Failing row contains (1, 1, [2025-01-01,2025-02-01), [2025-01-02,2025-02-28)).
    CONTEXT:  SQL statement "UPDATE ONLY "public"."b" SET "a" = $1, "a_p" = $2 WHERE $3
    OPERATOR(pg_catalog.=) "a" AND $4::pg_catalog.anyrange OPERATOR(pg_catalog.=)
    "a_p"::pg_catalog.anyrange"