database design - How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Question

I've been trying to sort out the relationship between unique and index in Postgres after reading the docs on index uniqueness being an implementation detail:

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 taking the docs at their word I'm going to just declare things as unique and use the implicit index - or - create an index and not assume that the values are unique. Is this a mistake??

What kind of index will I be getting from unique??Given that only a btree will accept the unique constraint and unique implicitly creates an index is it true that UNIQUE creates a btree index? I don't want to be running ranges on a hash index inadvertently.?

Answer 1

or - create an index and not assume that the values are unique

It is safe to assume that values are unique, if you have a unique index defined. That's how unique constraints are implemented (at the time being, and probably in all future versions as well).

Defining a UNIQUE constraint does effectively the same (almost, see below) as creating a unique index without specifying the index type. And, I quote the manual:

Choices are btree, hash, gist, and gin. The default method is btree.

Adding a constraint is just the canonical way that would not break in future versions where it could be implemented differently. That's all.

And no, a unique constraint can only be implemented with a basic btree index in all versions up to and including PostgreSQL 9.4. I quote the "ADD table_constraint_using_index" paragraph in the manual here:

The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering.

Other differences

• Unique constraints can be deferred. That is not possible for unique indexes. Have a look at the SET CONSTRAINTS command and follow the links for more.

- A foreign key cannot reference columns with just a unique index. The manual:

A foreign key must reference columns that either are a primary key or form a unique constraint.

The last bit seems to be outdated or a misunderstanding from the getgo. See:

• NULL values for referential_constraints.unique_constraint_* columns in information schema

Related:

• Is unique index better than unique constraint when I need an index with an operator class