is there unix timestamp range function in PostgreSQL 13

Question

I have a table that stored user subscribe information, now I want to add a constraint to make the user did not subscribe the product multiple times in the same peroid of time. What I am tried to do like this:

CREATE INDEX user_sub_exclusion_index ON user_sub USING GIST (
  user_id,
  tsrange(sub_start_time, sub_end_time)
);

and create constraint:

ALTER TABLE user_sub ADD CONSTRAINT user_sub_exclusion_constraint EXCLUDE USING GIST (
  user_id WITH =,
  tsrange(sub_start_time, sub_end_time) WITH &&
);

seems the tsrange need the timestamp data type. I have tried range function but did not work. what should I do to make the gist index work and did not change the data type? This is the table DDL:

CREATE TABLE public.user_sub (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    app_id varchar NOT NULL,
    product_id int4 NOT NULL,
    iap_product_id varchar NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    user_id int8 NOT NULL,
    sub_start_time int8 NOT NULL DEFAULT 0,
    sub_end_time int8 NOT NULL DEFAULT 0,
    enabled int2 NOT NULL DEFAULT 1,
    order_id varchar NOT NULL,
    CONSTRAINT user_sub_new_pk PRIMARY KEY (id),
    CONSTRAINT user_sub_new_un UNIQUE (order_id)
);

I have tried the change the field to timestamptz and tweak the sql like this:

CREATE INDEX user_sub_exclusion_index ON user_sub USING GIST (
  user_id,
  tstzrange(sub_start, sub_end)
);

show error :

SQL Error [42704]: ERROR: data type bigint has no default operator class for access method "gist"
  Hint: You must specify an operator class for the index or define a default operator class for the data type.

what should I do to add the constraint?

Answer 1

By default, PostgreSQL does not include GiST operator classes for base types like bigint. You have to create a standard extension that provides the required operator classes:

CREATE EXTENSION btree_gist;

Then you can create your constraint.

Note that adding an exclusion constraint automatically creates a GiST index.