Re: [GENERAL][SQL] Getting multiple field unique index to distinguish NULLs.

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.