Received: from itd.nrl.navy.mil (s2.itd.nrl.navy.mil [132.250.83.3]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA28469; Fri, 10 Sep 1999 15:06:49 -0400 (EDT) (envelope-from strohmayer@itd.nrl.navy.mil) Received: from itd.nrl.navy.mil (fw1-5540.itd.nrl.navy.mil [132.250.80.6]) by itd.nrl.navy.mil (8.8.8/8.8.8) with ESMTP id PAA19857; Fri, 10 Sep 1999 15:06:34 -0400 (EDT) Received: from osa (osa.fw5540.net [10.0.2.26]) by itd.nrl.navy.mil (8.9.0/8.9.0) with ESMTP id PAA26735; Fri, 10 Sep 1999 15:05:52 -0400 (EDT) Message-Id: <4.2.0.58.19990910145431.00a38340@pop> X-Sender: strohmay@pop X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.0.58 Date: Fri, 10 Sep 1999 15:10:05 -0400 To: Stuart Rison , Tom Lane From: Beth Strohmayer Subject: Re: [GENERAL][SQL] Getting multiple field unique index to distinguish NULLs. Cc: pgsql-general , pgsql-sql In-Reply-To: References: <10253.936975070@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Is this problem with NULLS being treated as distinct values only apparent in regards to Indices? If you do a Select Distinct or a Group By using these fields does it display the duplicate rows with nulls? I know this doesn't offer any help but I was just curious... Beth :-) _______________________________________________ / Beth L Strohmayer / Software Engineer _____) / ITT Industries, Systems Division (_____|______________________ / @ Naval Research Laboratory, Code 5542 | \ \ 4555 Overlook Ave. SW | Phone: (202) 404-3798 \ \ Washington, DC 20375 | Fax: (202) 404-7942 \ \_________________________________________| / | strohmayer@itd.nrl.navy.mil / |____________________________/ At 11:37 AM 09/10/1999 , Stuart Rison wrote: >Hi Tom, > >On Fri, 10 Sep 1999, Tom Lane wrote: > > > > The problem is that the indexing considers all NULLs to be distinct thus: > > > > Don't use NULLs ... declare the columns as NOT NULL, and select some > > special value for the fields you want to consider unused. Zero maybe, > > or an empty string if the columns are text. > > > >Fair enough, and indeed it's a solution I've been using but, for the sake >of argument, what if I couldn't do it? > >I though of using a index and functions such as: > >CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat >(level1, null_to_zero(level2), null_to_zero(level3), >null_to_zero(level4)); > >but saddly, you can't use functions in that way to define indices or at >least not in this way... > >I tried > >CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat >(make_key(level1,level2,level3,level4) text_ops); > >Where make_key essentially concatenates the levels into a '.' separated >key (e.g. 1 or 1.2.1 or 2.4.1.6). > >Now this would work except that there's a problem with pl/pgSQL such that >when you pass several values to a plpgsql function and one of them is >NULL, then all values passed to the function become NULL. > >The solution would be something like: > >CREATE UNIQUE INDEX myscheme_idx ON myscheme_funcat >(make_key(level1,null_to_zero(level2),null_to_zero(level3), >null_to_zero(level4)); > >But saddly you can't have "sub-function" in the CREATE INDEX statement. > >So: > >1) yes, the solution would be to use 0 or somesuch token value for 'NULL' >2) but I wondered if anybody had ventured into the kind of stuff mentioned >above >3) and if anybody have solved the pl/pgSQL 'issue' that turns all values >to NULL? > >Regards, > >Stuart. > > >************