pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Beth Strohmayer <strohmayer@itd.nrl.navy.mil>
To: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general <pgsql-general@postgresql.org>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: [GENERAL][SQL] Getting multiple field unique index to distinguish  NULLs.
Date: Fri, 10 Sep 1999 15:10:05 -0400
Message-ID: <4.2.0.58.19990910145431.00a38340@pop> (raw)
In-Reply-To: <Pine.LNX.4.10.9909101626270.821-100000@bsmlx17>
References: <10253.936975070@sss.pgh.pa.us>


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.
>
>
>************




view thread (6+ messages)  latest in thread

Message-ID: <4.2.0.58.19990910145431.00a38340@pop>
Permalink:  ../4.2.0.58.19990910145431.00a38340@pop/
Also on:    postgresql.org/message-id/4.2.0.58.19990910145431.00a38340@pop

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: strohmayer@itd.nrl.navy.mil, rison@biochemistry.ucl.ac.uk, tgl@sss.pgh.pa.us, pgsql-general@postgresql.org
  Subject: Re: [GENERAL][SQL] Getting multiple field unique index to distinguish  NULLs.
  In-Reply-To: <4.2.0.58.19990910145431.00a38340@pop>
  References: <10253.936975070@sss.pgh.pa.us> <4.2.0.58.19990910145431.00a38340@pop>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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