CODEWITHSUNDEEP
sqlpostgresqltimestampconstraints
Pieter-Jan Cassiman
Pieter-Jan Cassiman

Reputation: 27

Postgresql constraint no overlap between timestamps

I'm trying to create a table containing opening hours (for cafeteria's) However I am having some issues with implementing the constraint. For each resto, the openinghours cannot overlap.

CREATE table openinghours(
    id serial PRIMARY key,
    start timestamp NOT NULL,
    duration interval NOT NULL,
    resto_naam varchar REFERENCES resto(naam),
    constraint openingstijden_duplicate_check EXCLUDE USING GIST (
        resto_naam WITH =,
        /* Check wether there is no overlap between the dates */
        tsrange(start, start + duration) WITH &&
    )
);

when I try to execute this SQL I get the following error:

ERROR: data type character varying 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. SQL state: 42704

Any help would be appreciated!

Upvotes: 0

Views: 304

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

You need the btree_gist extension for this to work. Execute create btree_gist and try again.

Best regards,
Bjarni

Upvotes: 1

Related Questions