PostgreSQL上Bytea数据类型的唯一约束 [英] unique constraint on Bytea data type on Postgresql
问题描述
我将图像存储在Postgresql的Bytea数据类型上,我知道不建议这样做,但是我确实认为整理图像的好处明显不利于我使用。我面临的问题是我想确保仅插入唯一的图像,但是,当我创建唯一的约束时,会出现以下错误
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
错误:索引行需要28120字节,最大大小为8191
ERROR: index row requires 28120 bytes, maximum size is 8191
在将图像插入到文件中时如何检查唯一性bytea的Postgresql数据类型?
How can I check for uniqueness when inserting images into the bytea data type of Postgresql?
推荐答案
在摘要上创建唯一索引:
Create a unique index on a digest:
CREATE UNIQUE INDEX idx_image_hash ON images (digest(img, 'sha1'));
这假定您已安装pgcrypto扩展名:
This assumes you have the pgcrypto extension installed:
CREATE EXTENSION pgcrypto;
这篇关于PostgreSQL上Bytea数据类型的唯一约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!