Re: can UNIQUEness of TEXT datatype really be guaranteed?

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 ;)