NO$ tip: Postgres is [not] distinct from
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