To: Bruce Momjian <bruce@momjian.us>
cc: Juho Saarikko <juhos@mbnet.fi>, pgsql-bugs@postgresql.org
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values 
In-reply-to: <200802182212.m1IMCSD14587@momjian.us> 
References: <200802182212.m1IMCSD14587@momjian.us>
Comments: In-reply-to Bruce Momjian <bruce@momjian.us>
	message dated "Mon, 18 Feb 2008 17:12:28 -0500"
Date: Mon, 18 Feb 2008 17:25:46 -0500
Message-ID: <10241.1203373546@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bruce Momjian <bruce@momjian.us> writes:
> Juho Saarikko wrote:
>> While I didn't test, I'd imagine that this would also mean that any attempt
>> to insert such values to an already unique column would fail.

> Works here in 8.3:
	
> 	test=> create table test (x text unique);
> 	NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_x_key" for table "test"
> 	CREATE TABLE
> 	test=> insert into test values (repeat('a', 50000));
> 	INSERT 0 1

That test only works because it's eminently compressible.


The short answer to this bug report is that we're not very concerned
about fixing this because there is seldom a good reason to have an
index (unique or not) on fields that can get so wide.  As was already
noted, if you do need a uniqueness check you can easily make a 99.9999%
solution by indexing the md5 hash (or some similar digest) of the
column.  It doesn't really seem worthwhile to expend development work
on something that would benefit so few people.

			regards, tom lane