From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Roberto Caravani" <JFanatiker(at)gmx(dot)at> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: unique constraint |
Date: | 2012-04-17 19:18:53 |
Message-ID: | 13949.1334690333@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Roberto Caravani" <JFanatiker(at)gmx(dot)at> writes:
> I have a little problem with a unique constraint:
> create table meta_data (
> id serial primary key,
> type int not null,
> data bytea not null,
> unique (type, data)
> );
> data can be quite large (a few hundred kilo bytes). The unique constraint automatically creates a btree index as I learned from the documentation. The problem is that I get an error when inserting data, that the index is to large.
You mean that an index entry is too large, because you have a wide
"data" value?
The usual hack for that is to put a unique index on some hash of the
wide column, trusting that you won't get hash collisions. So for
instance
create unique index meta_data_unique on meta_data (type, md5(data));
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2012-04-18 07:10:40 | Re: Escaping literal strings in pg_8.4 |
Previous Message | ktm@rice.edu | 2012-04-17 19:16:02 | Re: unique constraint |