Thread: EXCLUDE USING hash(i WITH =)

EXCLUDE USING hash(i WITH =)

From

Erwin Brandstetter

Date:

06 March 2019, 21:00:26

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