How to check for null/empty/whitespace values with a single test in PostgreSQL-CodePudding

I would like to check an input (parameter) of a stored procedure if it has many spaces and empty, not just 1 space like so: ' ' '. I tried :

IF column IS NULL or TRIM(column IS NULL) THEN RAICE NOTICE 'input is empty spaces';
END IF;

But the spaces input still passes through.

CodePudding user response：

You could use COALESCE() here:

IF
    COALESCE(column, '') ~ '^\s*$'
THEN
    RAICE NOTICE 'input is empty spaces';
END IF;

The pattern ^\s*$ will match zero or more whitespace characters. The outer call to COALESCE() replaces NULL column values with empty string, which will match the regex pattern.

CodePudding user response：

TRIM(column IS NULL) should result in an error, because column is null yields a boolean and trim() doesn't work on boolean values.

Note that trim() will return an empty string if the input is only blank, not null (an empty string '' is something different than null)

You can shorten the whole test to a single expression:

if nullif(trim(column), '') is null then ...