Forum OpenACS Q&A: Notice: Found postgres7.2 incompatibility

In 7.1.2 you could do a select * from foo where bar=NULL and it will do what you expect. In 7.2 this will not return rows. You have to do: where bar is NULL

If you want to test this at home, here is a test script:

create table null_test (
       index integer primary key,
       value varchar
);

insert into null_test 
(index, value)
values
(1, NULL);

insert into null_test 
(index, value)
values
(2, 'baz');

select * from null_test where value=NULL;
select * from null_test where value is NULL;

drop table null_test;

     * The timestamp() function is no longer available.  Use timestamp
       'string' instead, or CAST.

In OpenACS 3.x there seems to be also one occurence of this that should be changed, here: www/education/class/admin/task-add-3.tcl - but I did that search on an old checkout and don't know if that file is still there, or what this education module does at all, so did not change it.

select interval(reltime '10 minutes')

This works fine in 7.1.2 and not in 7.2.

I only found this in some inhouse code, but you might trip over it.

cheers,

I'm using PG 7.1.2 and have many instances of =NULL and = NULL in mods I've made in OpenACS 3.2.x. So before I upgrade to PG 7.2 and beyond, I'll need to change many scripts. But I wonder, why wasn't =NULL just left in the PG code as a (depreciated) but still usable alternative?

Is it worth pursuing with the PG dev group or is it apt to be a worthless exercise?
OR
"Hey Bob, Just start making those changes from = NULL to is Null." ?

-Bob

foo = NULL returns true

foo < NULL returns NULL

See the inconsistency?  Three-valued logic is pervasive in SQL and implementors should never break it.  Making "foo = NULL" is an effort to hide three-valued logic from the user, and yes, I think it's access and I think it's because MS thought that hiding three-valued logic is easier than explaining it, and the "=" case is the one most non-sophisticated users will run into.

But you can't write complex SQL without fully understanding how three-valued logic works, and breaking standard SQL queries is not an aid to those writing portable code.  Which includes us, supporting multiple standard SQL RDBMS's is after all our goal!

Of course Oracle's "empty string = NULL in DML statements" is just as bad ...