2022 / Day 22: NULL

Null (as in database null) is a funny value - it can come in any type

select
    pg_typeof(null::geometry),
    pg_typeof(null::boolean),
    pg_typeof(null::int),
    pg_typeof(null::varchar),
    pg_typeof(null::bytea),
    pg_typeof(null::int[])
;

Null can be cast to literally any type in the database

In todays SQL lets look at what null is and null is not. The answers will be divided into three hexagons constructed with st_hexagon:

the unknown - where the output of the logical operation is null
and the boolean - where the output of the logical operation can be either true or false

the null-tests are organized as a single row in the CTE d and then transposed using PostgreSQL row_to_json and json_each_text functions to rows of keys and values.

output image

with
    d as (
        select
            r.value::boolean, count(1) as c, array_agg(key) as keys
        from (
            select
                null = null as "= null",
                null = false as "= false",
                null = true as "= true",
                null > false as "> false",
                null > true as "> true",
                null is distinct from null as "is distinct from null",
                null is not distinct from null as "is not distinct from null",
                null is false as "is false",
                null is true as "is true",
                null is null as "is null",
                null is distinct from false as "is distinct from false",
                null is distinct from true as "is distinct from true",
                null between true and false as "is between true and false",
                null between false and true as "is between false and true"
        ) f
            join lateral
                row_to_json(f.*) j on true
            join lateral
                json_each_text(j) r on true
        group by r.value::boolean
    ),
    g as (
        select
            v.*
        from (
            values (
                st_hexagon(4000, 0, 1), true
            ),(
                st_hexagon(4000, 0, 0), false
            ),(
                st_hexagon(4000, -1, 0), null
            )
        ) as v(geom, t)
    )
select
    row_number() over(order by cl)::int as oid,
    geom, value, key, cl
from (
    select
        geom, t::boolean as value,
        null as key, 'hex' as cl
    from
        g
    union all
    select
        st_buffer(dmp.geom,100) as geom, d.value,
        d.keys[dmp.path[1]] as key, 'test' as cl
    from
        g, d
            join lateral
                st_generatepoints(
                    st_buffer(g.geom, -1000),
                    d.c::int
                ) as pts on true
            join lateral
                st_dump(
                    pts
                ) as dmp on true
    where
        g.t is not distinct from d.value
) f
;