Re: NULL value comparison

On Aug 22, 2012, at 8:17 PM, Craig Ringer wrote:

> On 08/22/2012 10:58 PM, Michael Sacket wrote:
>> Thank you all very much!
>>
>> Unfortunately I can't change the query... but I can modify the data. I updated the NULL values to 'N' and put the appropriate NOT NULL constraint and a default value of 'N'.
>
>
> What tool/app is generating the query? They need to be told they're doing something wrong and unsafe - unless it's documented that the target column must be NOT NULL, anyway.
>
> PostgreSQL has a workaround for one such wrong, broken and unsafe program, Microsoft Access. However the workaround is limited to transforming "= NULL" to "IS NOT NULL"; it doesn't actually change the semantics of NULL.
>
> http://www.postgresql.org/docs/9.1/interactive/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS

Well... the query would be my fault, before I learned the value of having most columns NOT NULL. It's from an old WebObjects application. EOF at the time was generally not especially helpful with modeling boolean values and hooking them up to checkboxes so I (erroneously as it turns out) defined true to be 'Y' and anything else as false. In any case, it worked without issue until I switched to PostgreSQL yesterday and I didn't understand why. I was looking for the quickest route to a working version without having to coax EOF into generating the a different select.

The good news is I now have the proper constraints in place and the app and it's 130 tables are working with PostgreSQL in less than a day. Additionally, thanks to this list, I believe I understand the semantics of NULL now.

Thanks!
Michael