Does creating a unique constraint on a Postgres column remove the need to index it?

Question

Does creating a unique constraint on a Postgres column remove the need to index it?

I expect that an index is automatically needed to maintain the constraint efficiently.

Answer 1

Yes. A UNIQUE constraint is implemented with the help of a unique index - a b-tree index with default ascending sort ordering over all involved columns. The index is created and maintained automatically, and is used for all purposes like a plain unique index by Postgres.

There is no need to create another (redundant) unique index like it, that would be a waste of resources.

Detailed explanation:

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
• Is unique index better than unique constraint when an index with an operator class is required

Some rare exceptions apply for multicolumn indexes with special sort options:

• Optimizing queries on a range of timestamps (two columns)