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