Re: How to hint 2 coulms IS NOT DISTINCT FROM each other

> The bigger picture here is that if you've designed a data representation
> that requires that a null be considered "equal to" another null, you're
> really going to be fighting against the basic semantics of SQL. You'd
> be best off to rethink the representation. We've not seen enough info
> about your requirements to suggest just how, though.

Sometimes I do wake up in the night scared and afraid that I have used NULLs the wrong way
my whole life. I usually use NULLs to denote a value has not been provided. In my dreams I fear
I should have used empty string instead but if that is true, then I no longer know what is right and
wrong :).

In this specific case, its a resource management table. If a resource is not allocated to any
customers, then customer_id is set to null else the customer_id is set.

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, October 28, 2016 8:17:01 PM
To: Scott Marlowe
Cc: Kim Rose Carlsen; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen <krc(at)hiper(dot)dk> wrote:
>> I was wondering if there is a way to hint that two columns in two different
>> tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if
>> table_a.key = 'test' THEN table_b.key = 'test' .
>>
>> The equals operator already does this but it does not handle NULLS very well
>> (or not at all). And it seems IS NOT DISTINCT FROM is not indexable and
>> doesn't establish the same inference rules as equals.

> The whole idea behing Postgres' query planner is that you don't have
> to use any hints. Late model versions of postgres handle nulls fine,
> but nulls are never "equal" to anything else. I.e. where xxx is null
> works with indexes. Where x=y does not, since null <> null.

The bigger picture here is that if you've designed a data representation
that requires that a null be considered "equal to" another null, you're
really going to be fighting against the basic semantics of SQL. You'd
be best off to rethink the representation. We've not seen enough info
about your requirements to suggest just how, though.

regards, tom lane