From: Tom Lane <tgl@sss.pgh.pa.us>
To: fmiddleton@verizon.net
Cc: pgsql-sql@postgresql.org
Subject: Re: can UNIQUEness of TEXT datatype really be guaranteed?
Date: Tue, 22 Nov 2005 12:50:42 -0500
Message-ID: <12772.1132681842@sss.pgh.pa.us> (raw)
In-Reply-To: <2238206.1132680900217.JavaMail.root@vms068.mailsrvcs.net>
References: <2238206.1132680900217.JavaMail.root@vms068.mailsrvcs.net>
<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 UNIQUE something an uninformed, novice user would do?... or is it something indicative of the strength and/or weeknesses that separate 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, fmiddleton@verizon.net
Subject: Re: can UNIQUEness of TEXT datatype really be guaranteed?
In-Reply-To: <12772.1132681842@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox