Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Willy-Bas LoosDate: 2012-04-18 07:10:40
Subject: Re: Escaping literal strings in pg_8.4
Previous:From: ktm@rice.eduDate: 2012-04-17 19:16:02
Subject: Re: unique constraint

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group