IS NULL

All functions > COMPARISON > IS NULL

Returns TRUE if the expression evaluates to NULL.

Syntax

expr IS NULL [:: type]

expr IS NULL [:: type]

Notes

• Only reliable way to test for NULL values
• Using = NULL or != NULL always returns NULL, not TRUE/FALSE
• Works with any data type; optional ::T after the keywords disambiguates the expression when its type would otherwise be unclear (for example EMPTY from an unmatched RELATED); when ::T is present it must match the expression type (including ARRAY and ROW)
• Never returns NULL itself - always Returns TRUE or FALSE

Related Functions

• IS_NULL()

Examples

IS NULL — detection

SELECT
    f1 := NULL::BIGINT IS NULL::BIGINT, -- Typed NULL
    f2 := 1 IS NULL::BIGINT -- Non-NULL literal
;

IS NULL — composite values

SELECT
    f1 := NULL::ARRAY(BIGINT) IS NULL::ARRAY(BIGINT), -- Typed NULL array
    f2 := ARRAY(1, 2) IS NULL::ARRAY(BIGINT) -- Non-NULL array
;

IS NULL — contrast with =

SELECT
    f1 := NULL::BIGINT = NULL::BIGINT -- `=` cannot test NULL equality
;