PostgreSQL Unique Constraint with Exclusions

Question

I have a table:

CREATE TABLE dbo."TransportInteraction" ( "Id" bigint NOT NULL DEFAULT nextval('dbo."TransportInteraction_Id_seq"'::regclass), "Message" character varying(256), "TransportActionId" integer NOT NULL, "TimeCreated" timestamp without time zone NOT NULL, "TimeInserted" timestamp without time zone NOT NULL, "TaskId" bigint )

Generally, this table is mapping actions on a task. TransportActionId is an integer defining the type of action. Some action types must be unique per task and some others not.

So I need a constraint of type:
UNIQUE ("TaskId", "TransportActionId") applying to all actions with TransportActionId != (2||3 || 4).

So all actions except those with ActionId=2,3 or 4.

Any ideas?

Erwin Brandstetter · Accepted Answer · 2020-04-30 00:44:31Z

Use a partial unique index:

CREATE UNIQUE INDEX transint_partial_uni_idx ON dbo."TransportInteraction" ("TaskId", "TransportActionId") WHERE TransportActionId NOT IN (2,3,4);

Collectives™ on Stack Overflow

PostgreSQL Unique Constraint with Exclusions

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related