184

I'd like to add a constraint which enforces uniqueness on a column only in a portion of a table.

ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null);

The WHERE part above is wishful thinking.

Any way of doing this? Or should I go back to the relational drawing board?

EoghanM
  • 25,161
  • 23
  • 90
  • 123

2 Answers2

320

PostgreSQL doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index.

PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can't perform upserts (ON CONFLICT DO UPDATE) against a unique index like you would against a unique constraint.

Also, you won't see the constraint listed in information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

See partial indexes.

Carl Patenaude Poulin
  • 6,238
  • 5
  • 24
  • 46
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 35
    Super! Unintuitive that the "constraint" doesn't show up as a constraint, but nonetheless gives the desired error of `ERROR: duplicate key value violates unique constraint "stop_myc"` – EoghanM Apr 26 '13 at 17:08
  • 13
    It's worth to be noted that this won't allow creating FKs referencig that partially unique field. – ffflabs Oct 30 '17 at 22:02
  • 27
    It's also worth noting that this index effects can't be deferred. If you need to perform bulk updates this may present a problem as the uniqueness is checked after every row, not after the statement like it would be for a constraint or after the transaction like it would be for a deferrable constraint. – sage88 Mar 22 '18 at 07:21
  • 10
    Note, that you can't use this in `ON CONFLICT` expressions, because it requires actual constraint on table, not just index. – Pavlus Oct 29 '20 at 17:09
  • 1
    Implementing this in Doctrine ORM framework for PHP via annotations would look like this: `@Table(name="ecommerce_products",uniqueConstraints={@UniqueConstraint(name="stop_myc", columns={"col_a", "col_b"}, options={"where": "(col_b IS NOT NULL)"})})` as per docs: https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/annotations-reference.html#annref_uniqueconstraint – Eduard Sukharev Feb 11 '21 at 18:22
  • 10
    You apparently _can_ use partial unique indexes in `ON CONFLICT DO UPDATE`, you just have to basically redeclare the signature of the index. In your example, it would be `ON CONFLICT (col_a) WHERE (col_b is NOT null) DO UPDATE`. I'm on version 12; not sure when it was added. – ps2goat Feb 26 '22 at 00:54
72

it has already been said that PG doesn't define a partial (ie conditional) UNIQUE constraint. Also documentation says that the preferred way to add a unique constraint to a table is ADD CONSTRAINT Unique Indexes

The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

There is a way to implement it using Exclusion Constraints, (thank @dukelion for this solution)

In your case it will look like

ALTER TABLE stop ADD CONSTRAINT myc EXCLUDE (col_a WITH =) WHERE (col_b IS null);
epox
  • 9,236
  • 1
  • 55
  • 38
Peter Yeremenko
  • 1,219
  • 1
  • 11
  • 17
  • on that approach you don't use "using" to define index method, so that can be extremely slow or postgres creates a default index on that? That method is the canonical choice, but not ever the better choice! I think you gonna need a "using" clause with index to make that choice be the better one. – Natan Medeiros Feb 21 '18 at 11:40
  • 17
    While slower, the advantage of the exclude solution is that it is deferrable (and by default defers until the end of the statement). In contrast, the accepted unique index solution cannot be deferred (and is checked after every row change). So a bulk update is often not possible because steps during the update would violate the unique constraint, even if it wouldn't be violated at the end of the atomic update statement. – sage88 Mar 22 '18 at 08:45
  • 8
    This note was removed from the docs in [august 2015](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=049a7799dfce096923da27a9b0e4a3c7a0a47104) – Raniz Dec 18 '19 at 13:34