Re: can UNIQUEness of TEXT datatype really be guaranteed? - Mailing list pgsql-sql

From Tom Lane
Subject Re: can UNIQUEness of TEXT datatype really be guaranteed?
Date
Msg-id 12772.1132681842@sss.pgh.pa.us
Whole thread Raw
In response to can UNIQUEness of TEXT datatype really be guaranteed?  (<fmiddleton@verizon.net>)
List pgsql-sql
<fmiddleton@verizon.net> writes:
> So I can't help but wonder, can Postgres really guarantee a TEXT field to be UNIQUE... or is declaring a TEXT field
UNIQUEsomething an uninformed, novice user would do?... or is it something indicative of the strength and/or weeknesses
thatseparate the functionality of the two DBMSs.
 

In PG, it will work as long as no entry is too large to fit into a btree
index entry (from memory, about 2700 bytes after compression, so the
practical limit is probably 4KB or so).

If you think you might have entries exceeding a few KB, you could use
the trick of declaring a unique functional index on a checksum:create unique index myindex on mytable
(md5(fieldname));
This will work as long as you don't get any md5 hash collisions,
which is probably not a problem in practice.  It will guarantee
uniqueness in any case; the risk is that you might get false matches
causing rejection of inputs that actually are distinct.

A possibly simpler-to-understand way is to demand uniqueness in the
first couple KB:create unique index myindex on mytable (substr(fieldname,1,2000));
        regards, tom lane


pgsql-sql by date:

Previous
From: Jaime Casanova
Date:
Subject: Re: can UNIQUEness of TEXT datatype really be guaranteed?
Next
From: Kyle Bateman
Date:
Subject: Re: unplanned sub-select error?