pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan UNIQUEness of TEXT datatype really be guaranteed?
3+ messages / 3 participants
[nested] [flat]
* can UNIQUEness of TEXT datatype really be guaranteed?
@ 2005-11-22 17:35 fmiddleton@verizon.net
0 siblings, 2 replies; 3+ messages in thread
From: fmiddleton@verizon.net @ 2005-11-22 17:35 UTC (permalink / raw)
To: pgsql-sql
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 type 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 guarantee will 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 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.
Ferindo Middleton
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: can UNIQUEness of TEXT datatype really be guaranteed?
@ 2005-11-22 17:45 Jaime Casanova <systemguards@gmail.com>
parent: fmiddleton@verizon.net
1 sibling, 0 replies; 3+ messages in thread
From: Jaime Casanova @ 2005-11-22 17:45 UTC (permalink / raw)
To: fmiddleton@verizon.net <fmiddleton@verizon.net>; +Cc: pgsql-sql
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 VARCHAR type 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 guarantee will 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 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.
>
> 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 ;)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: can UNIQUEness of TEXT datatype really be guaranteed?
@ 2005-11-22 17:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: fmiddleton@verizon.net
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-11-22 17:50 UTC (permalink / raw)
To: fmiddleton@verizon.net; +Cc: 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 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-11-22 17:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 17:35 can UNIQUEness of TEXT datatype really be guaranteed? fmiddleton@verizon.net
2005-11-22 17:45 ` Jaime Casanova <systemguards@gmail.com>
2005-11-22 17:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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