Documentation enancement regarding NULL and operators
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t138552psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 04:01 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t138552_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t138552_1 && git checkout t138552_1Patchset v1 (message #1) is on t138552_1
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
On Sun, 2024-12-15 at 17:35 +0000, Luca Dametto wrote:
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.[not SQL, however]
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.".
There are already efforts to improve the documentation concerning NULL:
https://commitfest.postgresql.org/51/5086/
If that doesn't cover your case, perhaps you can join the discussion
and suggest improvements.
Yours,
Laurenz Albe
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@aklaver.com