NO$ tip: Postgres is [not] distinct from

[Phil (OmnisList)]

Interesting Doug,
especially the

where DateUpdated <> date ‘2017-01-01’     <—— will not give you null dates

Thanks for sharing...

regards
Phil Potter
Based in Chester in the UK.

On 22/09/2017 16:34, Doug Easterbrook 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)
>
> https://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 at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
>
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com