unique constraint on Bytea data type on Postgresql

Question

I'm storing images on the Bytea data type of Postgresql, I do understand that this is not recommend, but I do think the benefits of having the images organized out-wights the disadvantages for my use. The issue I'm facing is that I want to make sure only unique images get inserted, However when I create a unique constraint, I get the following error

ERROR: index row requires 28120 bytes, maximum size is 8191

How can I check for uniqueness when inserting images into the bytea data type of Postgresql?

Answer 1

Create a unique index on a digest:

CREATE UNIQUE INDEX idx_image_hash ON images (digest(img, 'sha1'));

This assumes you have the pgcrypto extension installed:

CREATE EXTENSION pgcrypto;