postgres btree index on large text column

Question

I have a table with big text column on which I want to enforce uniqueness, postgres can only do this with a btree index. I get the following error :

CREATE unique INDEX idx_protein_seq_sequence ON protein_seq USING hash (sequence);
[54000] ERROR: index row requires 8736 bytes, maximum size is 8191

Are there solutions ?

I'm thinking of using a trigger (update and insert) and find duplicates with a hash index (a hash index seems to have no limit on size of text columns);

Answer 1

You can use an exclusion constraint instead of a unique constraint:

alter table protein_seq add exclude using hash (sequence with =)

An exclusion constraint is much more general than a unique constraint, and allows you to exclude on things like overlapping ranges. When you exclude "with =", it reduces to basically the same thing as a unique constraint, except it retains the flexibility to use a hash index.