In postgresql, how can I make "name" unique only when "deleted" is false?

Question

I have the following table

CREATE TABLE "prm_project_service_product_and_services" ( "id" BIGSERIAL NOT NULL, "name" VARCHAR(60) NOT NULL, "note" VARCHAR(256) NOT NULL, "version" BIGINT DEFAULT NULL, "created_date" TIMESTAMP DEFAULT NULL, "created_by_id" BIGINT DEFAULT NULL, "last_modified_date" TIMESTAMP DEFAULT NULL, "last_modified_by_id" BIGINT DEFAULT NULL, "deleted" BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY ("id"), CONSTRAINT project_service_product_and_services_unique UNIQUE ("name") );

I want to make name unique only when deleted is false, is this possible ?

Possible duplicate. On an unrelated note, how would that work if deleted is turned back to true? — Ferox
– Ferox, Commented Feb 2, 2019 at 16:24

Gordon Linoff · Accepted Answer · 2019-02-02 16:24:59Z

You can use a partial unique index:

create unique index punq_prm_project_service_product_and_services on prm_project_service_product_and_services(name) where not deleted;

As explained in the documentation, this must be done with an index and not a unique constraint:

A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

Hi and thanks @GordonLinoff, Can't this be within the CREATE statement ?

Collectives™ on Stack Overflow

In postgresql, how can I make "name" unique only when "deleted" is false?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related