patternsqlModerate
UNIQUE constraint on large VARCHARs - PostgreSQL
Viewed 0 times
postgresqluniquelargevarcharsconstraint
Problem
I have a column defined like:
and UNIQUE constraint on that column:
When a large object is being inserted into the table, the following error message appears:
How can PostgreSQL be set in order to be able to index object larger that 8191 chars? Space and speed are not a problem. It is a rarely changed table of at most hundreds of rows.
Environment: PostgreSQL 9.3.6, Fedora 20 x64
data_url character varying(32768) NOT NULLand UNIQUE constraint on that column:
CONSTRAINT unique_data_url UNIQUE (data_url)When a large object is being inserted into the table, the following error message appears:
ERROR: index row requires 32584 bytes, maximum size is 8191How can PostgreSQL be set in order to be able to index object larger that 8191 chars? Space and speed are not a problem. It is a rarely changed table of at most hundreds of rows.
Environment: PostgreSQL 9.3.6, Fedora 20 x64
Solution
As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used:
create unique index unique_data_url_index on mytable (md5(data_url));
Context
StackExchange Database Administrators Q#94205, answer score: 17
Revisions (0)
No revisions yet.