Re: NO$ tip: Postgres is [not] distinct from

Thx Doug!

—
Learn something new every day!

> On 22 Sep 2017, at 17:34, Doug Easterbrook <doug@artsman.com> wrote:
>
> hi all.
>
> this stems from a hard slog last night trying to figure out why something was not working as we thought in the case of nulls, both in a stored procedure in pgsql and/or in select statements.
>
> and the root cause is nulls and what the database does with them in comparisons. I thoought I’d bring it up becase its such a similar thing in omnis when you use
>
> if isnull(field) (only checks null)
> if isclear(field) (checks for empty, null or zero)
>
> both of which behave differently, especially if there are added conditions to the if statement in omnis.
>
>
> back to postgres. the thing I never know is a clause called ’is distinct from’ (documentation here)
>
> www.postgresql.org/docs/9.6/static/functions-comparison.html
>
>
>
> if you run a statement like
>
> select 1<>null
>
> you might expect a FALSE to come back since 1 definitely is not = to null. instead, you get back an explicit null since ONE SIDE of the operator is null. apparently thats how postgres works.
>
>
> however a statement like
>
>
> select 1 is distinct from null will return TRUE or FALSE (depending on the statement)
>
>
> so why care? dates and timestamps perhaps. eg
>
>
>
> select * from myTable where DateUpdated <> date ‘2017-01-01’ <—— will not give you null dates >
>
> select * from myTable where DateUpdated is distinct from date ‘2017-01-01’ <— will include null dates in the search >
>
>
> or in a pgsql procedures, this does a far batter job of finding inequality
>
>
> if new.date is distinct from old.date then
> end if;
>
> than what we’d typically write, which fails to enter the if statement if either date is null.
>
>
> if new.date <> old.date then
> end if;
>
>
>
>
> took us 12 years to hit this small nugget of info… thought we’d share it.
>
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug@artsman.com
> www.artsman.com
> Phone (403) 650-1978
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com