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.
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 likely in all future).
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 Postgres 17. Quoting the manual at "ADD table_constraint_using_index":
The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering.
SET CONSTRAINTS command and follow the links for more.Related: