The manual currently advises: https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
EXCLUDE [ USING *index_method* ] ( *exclude_element* WITH *operator* [, ... > ] ) *index_parameters* [ WHERE ( *predicate* ) ][...]Although it's > allowed, there is little point in using B-tree or hash indexes with an > exclusion constraint, *because this does nothing that an ordinary unique > constraint doesn't do better*. So in practice the access method will > always be GiST or SP-GiST. However, hash indexes do not support UNIQUE: https://www.postgresql.org/docs/current/indexes-unique.html Currently, only B-tree indexes can be declared unique. > But an exclusion constraint with "USING hash" seems to do exactly that (more expensively, granted), handling hash collisions gracefully. Demo (original idea by user FunctorSalad on stackoverflow: https://stackoverflow.com/questions/47976185/postgresql-ok-to-use-hash-exclude-constraint-for-uniqueness/47976504?noredirect=1#comment96799970_47976504 ): CREATE TABLE exclude_hast_test( i int, EXCLUDE USING hash(i WITH =) ); INSERT INTO exclude_hast_test VALUES (213182),(1034649); -- hashint4() collision! More detailed fiddle: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8a9fc48f74f93f8aed0964f3796a0b04 Would seem particularly attractive for values too large for btree indexes. An index on a hash value is the recommended workaround, but an exclusion constraint also handles hash collisions automatically. (Or even for any wide column to keep index size low.) Hence my questions: - Why does an exclusion constraint with "USING hash(i WITH =)" enforce uniqueness, while we still can't create a "UNIQUE index ... USING hash .."? - Why would the manual discourage its use? Should I file a documentation bug? Regards Erwin
