Re: unique constraint

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

In response to

Browse pgsql-novice by date

  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