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
appropriateNOT 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
documentedthat 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
islimited 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
WebObjectsapplication.  EOF at the time was generally not especially helpful with modeling boolean values and hooking
themup 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
thequickest 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
inless than a day.  Additionally, thanks to this list, I believe I understand the semantics of NULL now.   

Thanks!
Michael