CODEWITHSUNDEEP
postgresqlindexingunique
Green
Green

Reputation: 5000

Postgres hash index with unique constraint

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?

Upvotes: 18

Views: 11533

Answers (4)

Alex Povolotsky
Alex Povolotsky

Reputation: 402

Very old idea

create unique index on links (cast(md5(url) as uuid));

I'm trying it now

UPD. It's slow like it's recalculating every sum on every insert.

UPD2. Adding functional unique index does not speed up SEARCH on the column.

To find the string in question one should either search by the same cast or add a hash index.

Upvotes: -1

Siwei
Siwei

Reputation: 21559

why not just simply add a constraint for this column ?

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

-- simply create a new constraint for this column, do not mix it with hash index
alter table t add constraint unique_id unique (id);

Upvotes: 0

jbg
jbg

Reputation: 5266

You can achieve this using an exclusion constraint:

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

Upvotes: 9

klin
klin

Reputation: 121594

The documentation leaves no room for doubt:

Currently, only B-tree indexes can be declared unique.

There was a discussion on the hackers list about this recently, and it was concluded that it wouldn't be simple to add the capability to allow UNIQUE hash indexes.

Upvotes: 18

Related Questions