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?

Answer 1

Use a partial unique index:

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

Related:

• Create unique constraint with null columns
• Unique combination in a table