Thread: BUG #1748: Unique contraints cannot be added to long text fields

The following bug has been logged online:

Bug reference:      1748
Logged by:          Greg Steffensen
Email address:      greg.steffensen@gmail.com
PostgreSQL version: 8.02
Operating system:   Gentoo Linux
Description:        Unique contraints cannot be added to long text fields
Details:

I'm storing SVG files, which can be many kilobytes long, in a text field.
When I try to add a unique contraint to that field, or try to add a row to a
table that already has had the unique contraint applied, I get errors like
the following:

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

Evidently, the unique constraint creates an index, but the index can't
handle large field sizes.

On Sat, Jul 02, 2005 at 05:20:29PM +0100, Greg Steffensen wrote:

> I'm storing SVG files, which can be many kilobytes long, in a text field.
> When I try to add a unique contraint to that field, or try to add a row to a
> table that already has had the unique contraint applied, I get errors like
> the following:
>
> ERROR:  index row requires 15528 bytes, maximum size is 8191
>
> Evidently, the unique constraint creates an index, but the index can't
> handle large field sizes.

Yes, that's a known limitation -- you cannot store arbitrarily large
fields in an index.  Even if it was possible I don't think it'd be a
good idea from an efficiency POV.  You may want to create an index in a
digested version of the file instead (SHA-1, say, or MD5).

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"El conflicto es el camino real hacia la unión"