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?
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.
You can achieve this using an exclusion constraint:
create table t (id int);
alter table t add constraint c exclude using hash (id with =);
conn.execute( f"""INSERT INTO {TABLE_NAME} VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET value = ($2)""", hash_id, str_profile, ) gives me there is no unique or exclusion constraint matching the ON CONFLICT specification
ON CONFLICT clause instead of making Postgres infer it from the column (which only works with unique indexes, not with exclusion constraints). In the example in my answer it would be ON CONFLICT (c) DO .... See the docs: postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICTON CONFLICT ON CONSTRAINT cnp_profiles_id_excl DO UPDATE which results in WrongObjectTypeError: ON CONFLICT DO UPDATE not supported with exclusion constraints I can't use hash indexes with this kind of upserts.
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);
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.
