btree_gist constraint example

[jlengelsen]

Your Question

I'm trying to implement a btree_gist constraint with gorm struct tags. It should check that there are no overlapping date ranges for each individual foreign key reference that is not soft deleted. I've read the docs about database indexes but I didn't manage to get the tag right.

These are SQL statements that provide a basic example (the constraint in question is "chk_overlapping_dates"):

CREATE EXTENSION btree_gist;

CREATE TABLE "other_table" (
  "id" bigserial NOT NULL,
  "created_at" timestamptz NULL,
  "updated_at" timestamptz NULL,
  "deleted_at" timestamptz NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "my_table" (
  "id" bigserial NOT NULL,
  "created_at" timestamptz NULL,
  "updated_at" timestamptz NULL,
  "deleted_at" timestamptz NULL,
  "other_id" bigint NOT NULL,
  "valid_from" timestamptz NOT NULL,
  "valid_until" timestamptz NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "fk_other_table" FOREIGN KEY ("other_id") REFERENCES "other_table" ("id"),
  CONSTRAINT "chk_valid_until" CHECK (valid_until > valid_from)
);

ALTER TABLE "my_table"
ADD CONSTRAINT "chk_overlapping_dates" EXCLUDE USING gist (
    other_id WITH =,
    tstzrange(valid_from, valid_until) WITH &&
  )
WHERE (deleted_at IS NULL);

Could you provide an example for how I could achieve that with gorm tags?

The document you expected this should be explained

https://gorm.io/docs/indexes.html

Expected answer

An example that implements the constraint in question with gorm struct tags.