1

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);

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.