I have the following query:
CREATE OR REPLACE VIEW muni_no_one_point_inside AS
SELECT
m.gid
, scripts.stx_extract(m.geom, 2)
FROM muni m
LEFT JOIN LATERAL (
SELECT ST_Collect(x.geom) as geom
FROM (
SELECT geom
FROM munitextosmal mtm
WHERE mtm.geom && m.geom
AND ST_Relate(m.geom, mtm.geom, 'T********')
)x
) as xx ON true
WHERE st_numGeometries(
st_intersection(m.geom, xx.geom)
) <> 1
That is, I want to obtain the geometries that either do not intersect with any point (NULL) or that intersect with more than one point .
With the previous query, the NULL values are not taken, I have to force it to take the NULL values, that is, in the WHERE clause I must check that the number of geometries is NULL or different from 1.
WHERE st_numGeometries(
st_intersection(m.geom, xx.geom)
) IS NULL OR st_numGeometries(
st_intersection(m.geom, xx.geom)
) <> 1
My question is, why isn't it enough to check that it is different from 1?
How does PostgreSQL internally decide that NULL is not different from 1?
Postgres indicates in its documentation that to compare if a value is NULL, this comparison must be done with IS NULL, since NULL itself is an indeterminate value. It even explicitly specifies that it cannot be compared to a NULL=NULL.
It seems that it is the official SQL specification that establishes this behavior. More information