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?

• user1822: You will have to convert the dreaded unix epoch to a proper timestamp (or timestamptz) in order to be able to build a tsrange (or tstzrange)
• user1822: Related
• Dolphin: I have read that post and I think the unix timestamp is better than timestamp, because the timetamp is hard to interop, you have to handle the timezone leap month leap year and so on. @a_horse_with_no_name
• user1822: Well, Postgres will handle all that for you if you are using timestamptz. But if you do want to use a tsrange you will have to convert that number to a proper timestamp at one point or another.
• Dolphin: seems timestamptz still could not created the index.

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.