Search Postgresql Archives

Re: Documentation enancement regarding NULL and operators

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 12/15/24 09:35, Luca Dametto wrote:
Hi All,
I'm coming from hours of debugging a SQL trigger that wasn't working properly. After a beautiful headache and infinite hours of documentation reading I've found out that something doesn't work as I would expect.

Most programming languages return "true" when two null values are compared, and false when, being the two values nullable, one of them is null and the other one isn't. Any developer coming from Python, Javascript, PHP (and many more) would expected 'example'= null to return false, whilst SQL thanks to 3VL returns you a gentle ¯\_(ツ)_/¯ . Not a true, not a false, just nothing - in a boolean statement.

Python3:
 >>> "example" == None
False
 >>> None == None
True

NodeJS:
 > "example" == null
false
 > null == null
true

PHP 8:
 > var_dump("example" == null);
bool(false)
 > var_dump(null == null);
bool(true)

Whilst I'd love to discuss the reasons of this, I understand that it would be a waste of time for everyone, as we cannot change the status-quo even if it made sense, as it would break many thousands of projects.

For that reason, I'd just like to improve the documentation to add at least a note about "hey, this won't work as you might expect, because it works in a different way than 99% of programming languages out there.". I've tried to understand how to submit my proposal for the documentation improvements, but it's way harder than what my brain can handle with the current headache caused by this stuff, I've attached a git patch to this email in case it's useful.

Content: "
PostgreSQL follows SQL's 3VL, due to that some comparisons regarding NULL values may not work as you might expect. As an example, two nullable columns that contain NULL, when compared using the OPERATOR =, will return nothing instead of TRUE like your programming language may do. In this case, only 'IS NOT DISTINCT FROM' would return the result you expect.
"

See:

https://www.postgresql.org/docs/current/functions-comparison.html

" Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM predicates:"



Kind regards,
Luca

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux