Обсуждение: can UNIQUEness of TEXT datatype really be guaranteed?
I am in the process re-implementing some pgsql table declarations into a MySQL database. I have this one line where I say ... fieldname TEXT UNIQUE, ... in my pgsql table because I want to ensure that no one enters the same thing in this field and I didn't choose VARCHAR typebecause I have no idea how possibly long I might need to let the field be... ... but MySQL doesn't like this because a TEXT datatype is considered a BLOB that can't be indexed and it can't guaranteewill be UNIQUE apparently... 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. Ferindo Middleton
On 11/22/05, fmiddleton@verizon.net <fmiddleton@verizon.net> wrote: > I am in the process re-implementing some pgsql table declarations into a MySQL database. I have this one line where I say > ... > fieldname TEXT UNIQUE, > ... > in my pgsql table because I want to ensure that no one enters the same thing in this field and I didn't choose VARCHARtype because I have no idea how possibly long I might need to let the field be... > > ... but MySQL doesn't like this because a TEXT datatype is considered a BLOB that can't be indexed and it can't guaranteewill be UNIQUE apparently... > > 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. > > Ferindo Middleton > TEXT fileds can be indexed in postgres and AFAIK it can be indexed with a unique index (and i don't see any good reason to think it can't guarantee uniqueness)... but you have a limitation, btree index rows can only have certain size (i don't remember the numbers now)... so if you exceed that size (and in a text field you can) you will get an error... you have to control within your application that... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)
<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