Documentation enancement regarding NULL and operators

From: "Luca Dametto" <postgresqlmailing(at)damettoluca(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Documentation enancement regarding NULL and operators
Date: 2024-12-15 17:35:16
Message-ID: emc054a8a5-7ede-476f-8bea-0558d655bbd1@6050b6ba.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
"

Kind regards,
Luca

Attachment Content-Type Size
0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch application/octet-stream 1.7 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Isaac Morland 2024-12-15 17:40:12 Re: Request for new column in pg_namespace
Previous Message Tom Lane 2024-12-15 17:29:26 Re: Request for new column in pg_namespace