Question 284742 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What is the difference between unique index and constaraint unique in postgres?

The first way to guarantee unique of many fields in to use constraint

create table t
(
    id bigserial primary key,
    val1 integer,
    val2 integer,
    unique (val1, val2)
);

On the other hand, we can use unique index

create table t
(
    id bigserial primary key,
    val1 integer,
    val2 integer
);

create unique index unique_val1_val2
on t (val1, val2);

is there any difference here? mb they will work differently with null values?

I have not noticed any difference while testing

Tags:

2

Answers


  1. 0

    From the manual:

    Adding a unique constraint will automatically create a unique B-tree
    index on the column or group of columns listed in the 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.

    The handling of NULLs depends on you, NULLS NOT DISTINCT or NULLS DISTINCT.

    Login or Signup to reply.
  2. 0

    The difference is none*. They enforce uniqueness exactly the same way and they offer almost the exact same level of control over the index: you’re right that one of the differences is related to null handling. Demo:

    create table t
(
    id bigserial primary key,
    val1 integer,
    val2 integer,
    unique nulls not distinct (val1 /*desc nulls last*/) 
        include(val2)
        with(fillfactor=100)
        using index tablespace pg_default
        --where id%2=1
        deferrable initially deferred
);

    The parts that are commented out above cannot be defined within constraint syntax but they are allowed by the equivalent index syntax, and vice versa: indexes don’t accept deferrability behaviour spec.

    create table t2
(
    id bigserial primary key,
    val1 integer,
    val2 integer
);
create unique index on t2 (val1 desc nulls last) 
  include(val2)
  nulls not distinct 
  with(fillfactor=100)
  tablespace pg_default
  where id%2=1
  /*deferrable initially deferred*/;

    There’s also a third option to enforce uniqueness, an exclusion constraint:

    create table t3
(
    id bigserial primary key,
    val1 integer,
    val2 integer,
    exclude (val1 desc nulls last with =)
        include(val2)
        with(fillfactor=100)
        using index tablespace pg_default
        where (id%2=1)
        deferrable initially deferred
);

    And this time around, while it does also use an index under the hood, it does not work the same:

    If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint, although an ordinary unique constraint will be faster. However, exclusion constraints can specify constraints that are more general than simple equality.

    It’s also harder to set up for multiple fields because you need to somehow merge them into one (array, json, range etc.).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search