Postgres Pro
Downloads
Home   >   mailing lists

Thread: unique constraint

unique constraint

From
"Roberto Caravani"
Date:
Hi!

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
learnedfrom the documentation. The problem is that I get an error when inserting data, that the index is to large. I
wouldlike to keep the unique constraint, is there a way to fix this? 

I googled the problem and found others having similar issues, but I have not found a solution that suited me.

Thanks in advance!

Best regards,

Robert
--
NEU: FreePhone 3-fach-Flat mit kostenlosem Smartphone!
Jetzt informieren: http://mobile.1und1.de/?ac=OM.PW.PW003K20328T7073a

Re: unique constraint

From
"ktm@rice.edu"
Date:
On Tue, Apr 17, 2012 at 07:46:26PM +0200, Roberto Caravani wrote:
> Hi!
>
> 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
learnedfrom the documentation. The problem is that I get an error when inserting data, that the index is to large. I
wouldlike to keep the unique constraint, is there a way to fix this? 
>
> I googled the problem and found others having similar issues, but I have not found a solution that suited me.
>
> Thanks in advance!
>
> Best regards,
>
> Robert

Hi Robert,

You may need to store a crypographic signature for the data and unique that instead.

Regards,
Ken

Re: unique constraint

From
Tom Lane
Date:
"Roberto Caravani" <JFanatiker@gmx.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
learnedfrom 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

Re: unique constraint

From
"Roberto Caravani"
Date:
Wow, that was fast. Thanks a lot Tom Lane and Ken!

This seems to work, thanks!
create unique index meta_data_unique on meta_data (type, md5(data));

Best regards,

Robert


--
NEU: FreePhone 3-fach-Flat mit kostenlosem Smartphone!
Jetzt informieren: http://mobile.1und1.de/?ac=OM.PW.PW003K20328T7073a