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

by

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 v14. Quoting the paragraph “ADD table_constraint_using_index” in the manual:

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.

Related:

More Related Contents:

  1. Does a Postgres UNIQUE constraint imply an index?
  2. Do nullable columns occupy additional space in PostgreSQL?
  3. PostgreSQL LIKE query performance variations
  4. Calculating and saving space in PostgreSQL
  5. Store common query as column?
  6. Postgresql: Conditionally unique constraint
  7. Making sense of Postgres row sizes
  8. Can PostgreSQL have a uniqueness constraint on array elements?
  9. Why does PostgreSQL perform sequential scan on indexed column?
  10. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  11. CONSTRAINT to check values from a remotely related table (via join etc.)
  12. Grant all on a specific schema in the db to a group role in PostgreSQL
  13. Difference between GiST and GIN index
  14. Postgres query optimization (forcing an index scan)
  15. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  16. Optimize Postgres query on timestamp range
  17. Multicolumn index on 3 fields with heterogenous data types
  18. PostgreSQL Index on JSON
  19. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  20. How to add a conditional unique index on PostgreSQL
  21. PostgreSQL – set a default cell value according to another cell value
  22. PostgreSQL composite primary key
  23. Optimize GROUP BY query to retrieve latest row per user
  24. How to implement a many-to-many relationship in PostgreSQL?
  25. INSERT with dynamic table name in trigger function
  26. Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)
  27. How to thoroughly purge and reinstall postgresql on ubuntu? [closed]
  28. PostgreSQL function returning multiple result sets
  29. How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
  30. no pg_hba.conf entry for host

Leave a Comment