X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4DADDAC9C for ; Tue, 22 Nov 2005 13:45:39 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83950-08 for ; Tue, 22 Nov 2005 17:45:38 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195]) by svr1.postgresql.org (Postfix) with ESMTP id 1D536D86C2 for ; Tue, 22 Nov 2005 13:45:36 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i7so1127483wra for ; Tue, 22 Nov 2005 09:45:36 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=g5YFiNbEFYG6kNJuOzRyM4YZXLuhCeeJviGkP+0BKoYwKM9X2shzsNhC2NzNI5XadgMioogH02FkwWEjeARFaOQJk4yBei3DHqo1bFlX/Z6pIlpF7BDafNfCTX3alHYJZDZPZZ3rGmpALk480VRazUxxdMBneL3wFNzqa11DKF8= Received: by 10.64.196.19 with SMTP id t19mr4742639qbf; Tue, 22 Nov 2005 09:45:36 -0800 (PST) Received: by 10.65.180.2 with HTTP; Tue, 22 Nov 2005 09:45:36 -0800 (PST) Message-ID: Date: Tue, 22 Nov 2005 12:45:36 -0500 From: Jaime Casanova To: "fmiddleton@verizon.net" Subject: Re: can UNIQUEness of TEXT datatype really be guaranteed? Cc: pgsql-sql@postgresql.org In-Reply-To: <2238206.1132680900217.JavaMail.root@vms068.mailsrvcs.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <2238206.1132680900217.JavaMail.root@vms068.mailsrvcs.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.02 required=5 tests=[AWL=0.020] X-Spam-Score: 0.02 X-Spam-Level: X-Archive-Number: 200511/208 X-Sequence-Number: 23459 On 11/22/05, 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 th= ing in this field and I didn't choose VARCHAR type because I have no idea h= ow possibly long I might need to let the field be... > > ... but MySQL doesn't like this because a TEXT datatype is considered a B= LOB 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 ;)