Postgres unique constraint vs index

One more advantage of using UNIQUE INDEX vs. UNIQUE CONSTRAINT is that you can easily DROP/CREATE an index CONCURRENTLY, whereas with a constraint you can't.

Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists.

Conceptually the index is an implementation detail and uniqueness should be associated only with constraints.

The full text

So speed performance should be same

Since various people have provided advantages of unique indexes over unique constraints, here's a drawback: a unique constraint can be deferred (only checked at the end of the transaction), a unique index can not be.

A very minor thing that can be done with constraints only and not with indexes is using the ON CONFLICT ON CONSTRAINT clause (see also this question).

This doesn't work:

CREATE TABLE T (a INT PRIMARY KEY, b INT, c INT);
CREATE UNIQUE INDEX u ON t(b);

INSERT INTO T (a, b, c)
VALUES (1, 2, 3)
ON CONFLICT ON CONSTRAINT u
DO UPDATE SET c = 4
RETURNING *;

It produces:

[42704]: ERROR: constraint "u" for table "t" does not exist

Turn the index into a constraint:

DROP INDEX u;
ALTER TABLE t ADD CONSTRAINT u UNIQUE (b);

And the INSERT statement now works.

Another thing I've encountered is that you can use sql expressions in unique indexes but not in constraints.

So, this does not work:

CREATE TABLE users (
    name text,
    UNIQUE (lower(name))
);

but following works.

CREATE TABLE users (
    name text
);
CREATE UNIQUE INDEX uq_name on users (lower(name));

There is a difference in locking.
Adding an index does not block read access to the table.
Adding a constraint does put a table lock (so all selects are blocked) since it is added via ALTER TABLE.

As of PostgreSQL 18, both still end up enforcing uniqueness exactly the same way but aren't equivalent in terms of how they command the underlying index. There's also a third way to do unique, in the form of exclusion constraints (since 9.0, but below matrix refers to state as of 18):

In general, the price for the flexibility of an exclusion constraint is performance:

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.

Demo at db<>fiddle

Constraint:

create table t (
    id bigserial primary key,
    val1 text,
    val2 text,
    unique /*concurrently*/
        nulls not distinct 
       (--(val1||val2) --expression
         val1
       /*collate unicode 
         text_ops 
         desc nulls last
         with operator(pg_catalog.=)*/) 
        include(val2)
        with(fillfactor=100)
        using index tablespace pg_default
        --where (id%2=1)
        deferrable initially deferred
);

Index:

create table t2 (
    id bigserial primary key,
    val1 text,
    val2 text
);
create unique index concurrently 
  on t2 ((val1||val2)--expression
         collate unicode 
         text_ops 
         desc nulls last
         /*with operator(pg_catalog.=)*/) 
  include(val2)
  nulls not distinct 
  with(fillfactor=100)
  tablespace pg_default
  where id%2=1
  /*deferrable initially deferred*/;

Index added as constraint:

create unique index concurrently uidx
  on t3 (--(val1||val2)--expression
         val1 
         /*collate unicode 
         text_ops 
         desc nulls last
         with operator(pg_catalog.=)*/) 
  include(val2)
  nulls not distinct 
  with(fillfactor=100)
  tablespace pg_default
  /*where id%2=1*/;
alter table t3 
  add constraint ucns 
  unique using index uidx 
  deferrable initially deferred;

Exclusion:

create table t4 (
    id bigserial primary key,
    val1 text check(val1<>'reserved'),
    val2 text,
    exclude /*concurrently*/
            (coalesce(val1,'reserved') --expression
             collate unicode 
             text_ops 
             desc nulls last 
             with operator(pg_catalog.=))
        include(val2)
        with(fillfactor=100)
        using index tablespace pg_default
        where (id%2=1)
        deferrable initially deferred
);

In addition to the other answers, there's a topic of whether unique constraints are also used to speed-up queries as indexes are.

Apparently constraints are actually used for Index Scans as indicated by EXPLAIN:

ALTER TABLE mytable
    ADD CONSTRAINT mytable_uc UNIQUE (other_id, name);

explain select * from mytable
    where name = 'name' and other_id = 154

Result:

Index Scan using mytable_uc on mytable  (cost=0.28..2.29 rows=1 width=101)
  Index Cond: ((other_id = 154) AND ((name)::text = 'name'::text))

I read this in the doc:

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

So I think it is what you call "partial uniqueness" by adding a constraint.

And, about how to ensure the uniqueness:

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.

Note: 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.

So we should add constraint, which creates an index, to ensure uniqueness.

How I see this problem?

A "constraint" aims to gramatically ensure that this column should be unique, it establishes a law, a rule; while "index" is semantical, about "how to implement, how to achieve the uniqueness, what does unique means when it comes to implementation". So, the way Postgresql implements it, is very logical: first, you declare that a column should be unique, then, Postgresql adds the implementation of adding an unique index for you.

As I found in ibm descriptions:

In DML statements, enabled unique constraints on a logged table are checked at the end of a statement, but unique indexes are checked on a row-by-row basis, thereby preventing any insert or update of a row that might potentially violate the uniqueness of the specified column (or for a multiple-column column constraint or index, the column list).

For example, if you stored the values 1, 2, and 3 in rows of a logged table that has an INT column, an UPDATE operation on that table that specifies SET c = c + 1 would fail with an error if there were a unique index on the column c, but the statement would succeed if the column had a unique constraint. Blockquote

There seems to be an important difference between the Postgresql and DB2 implementation of a unique index across multiple fields, which include fields that can be null

For example on a table like:

create table if not exists bus_target_log
    (k_targetlog_id                    varchar(36)     primary key
    ,c_message_id                      varchar(50)     not null
    ,c_lep_name                        varchar(50)
    ,c_unique                          varchar(36)
    ,d_delivered_timestamp             timestamp       not null default statement_timestamp()
    ,c_target                          varchar(30)     not null)

I add a unique index:

create unique index if not exists bus_target_log_ix1
    on bus_target_log
    (c_unique                          asc
    ,c_message_id                      asc
    ,c_lep_name                        asc
    );

If I know insert the following on DB2, I would get an error on the second insert.

insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target) values ('4444','111', 'targetname1', 'target'); -- works
insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target) values ('5555','111', 'targetname1', 'target'); -- fails

However on Postgresql both inserts work!

On postgresql I can run the following:

insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target, c_unique) values ('1','111', 'targetname1', 'target','1');
-- works
insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target, c_unique) values ('2','111', 'targetname1', 'target','1');
-- fails
insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target) values ('3','111', 'targetname1', 'target');
-- works
insert into bus_target_log(k_targetlog_id, c_message_id, c_lep_name, c_target) values ('4','111', 'targetname1', 'target');
-- works

This can be fixed in Postgresql by ensuring all fields in the unique index are not null, for example

    create table if not exists bus_target_log
(k_targetlog_id                    varchar(36)     primary key
,c_message_id                      varchar(50)     not null
,c_lep_name                        varchar(50)     not null default '0'
,c_unique                          varchar(36)     not null default '0'
,d_delivered_timestamp             timestamp       not null default statement_timestamp()
,c_target                          varchar(30)     not null)

SELECT a.phone_number,count(*) FROM public.users a
Group BY phone_number Having count(*)>1;

SELECT a.phone_number,count(*) FROM public.retailers a
Group BY phone_number Having count(*)>1;

select a.phone_number from users a inner join users b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id;


select a.phone_number from retailers a inner join retailers b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id

DELETE FROM
    users a
        USING users b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
DELETE FROM
    retailers a
        USING retailers b
WHERE
    a.id > b.id
    AND a.phone_number = b.phone_number;
    
CREATE UNIQUE INDEX CONCURRENTLY users_phone_number 
ON users (phone_number);

To Verify:

insert into users(name,phone_number,created_at,updated_at) select name,phone_number,created_at,updated_at from users