Postgres hash index with unique constraint

user_2910052 0
Viewed 5825

With Postgres 10 properly supporting hash index, I would like to use hash index for id lookup (hash index is smaller in size compared to btree and theoretically faster).

I have a table 

create table t (id int);
create unique index on t using hash (id);

But I got the following:

ERROR: access method "hash" does not support unique indexes

Why does hash index not allow unique constraint? Are there ways to circumvent this?

unique indexing postgresql
2 Answers

You can achieve this using an exclusion constraint:

create table t (id int);
alter table t add constraint c exclude using hash (id with =);
user_11745210
Related