Question 114000 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search