Re: PRIMARY KEY on a *group* of columns imply that each column is NOT

On Thu, Apr 28, 2005 at 02:04:29PM +0200,
Marco Colombo <pgsql(at)esiway(dot)net> wrote
a message of 146 lines which said:

> No. NULL is NOT 'None', nor 'undef', and definitely not NULL as in
> C.

Thanks for the very good and detailed explanation of NULL in
SQL. Curious people may note that the strange semantics of NULL are
heavily criticized in C. J. Date and Hugh Darwen "A Guide to the SQL
Standard" (Addison-Wesley) [Side note: I was perfectly aware for NULL
in C, where it is just an ordinary zero, that's why I only mentioned
Perl and Python.]

> marco=# select 2 = NULL;
> ?column?
> ----------
>
> (1 row)

Even better, you can write;

registry=> select NULL = NULL;
?column?
----------

(1 row)

> Depending on what you're trying to achieve, you may need to split
> the table (normalization the theorists call it).

Yes, I noticed in similar organizations that the Hosts table was split
in one table for names and one for addresses, may be for exactly that
reason.

> Review your design, maybe either the table schema or the choice of
> the primary key is not natural for your database.

At the present time, it seems that, practically speaking, the
technique proposed by Sebastian Böck (two index) is perfectly
fine. This technique can be summarized as "PostgreSQL, I tell you to
treat all NULL addresses as being the same value".