´´´
DROP TABLE A;
CREATE TABLE A ( id CHARACTER VARYING(255) NOT NULL PRIMARY KEY, modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
category CHARACTER VARYING(255) NOT NULL, is_active boolean NOT NULL DEFAULT TRUE
);
CREATE UNIQUE INDEX A_UNIQUE ON A(category) WHERE is_active=TRUE;
CREATE UNIQUE INDEX A_UNIQUE ON A(category) WHERE valid_to>'now'::TIMESTAMP;
CREATE TABLE A ( id CHARACTER VARYING(255) NOT NULL PRIMARY KEY, created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
category CHARACTER VARYING(255) NOT NULL, valid_from TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), valid_to TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2030-01-01 00:00:00'
);
CREATE UNIQUE INDEX A_UNIQUE ON A(category) WHERE 'now'::TIMESTAMP BETWEEN valid_from AND valid_to;
https://dba.stackexchange.com/questions/110582/uniqueness-constraint-with-date-range
CREATE TABLE A ( id CHARACTER VARYING(255) NOT NULL PRIMARY KEY, created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), modified_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
product_id CHARACTER VARYING(255) NOT NULL, category CHARACTER VARYING(255) NOT NULL, valid_from TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(), valid_to TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2030-01-01 00:00:00'
);
--CREATE UNIQUE INDEX A_UNIQUE ON A(category) WHERE 'now'::TIMESTAMP BETWEEN valid_from AND valid_to; ALTER TABLE A DROP CONSTRAINT unique_category_per_per_product_and_daterange;
CREATE EXTENSION btree_gist; ALTER TABLE A ADD CONSTRAINT unique_category_per_per_product_and_daterange EXCLUDE USING gist ( --id WITH =, product_id WITH =, category WITH =, TSRANGE(valid_from, valid_to, '[]') WITH && -- this is the crucial );
´´´