1

I have created a function in Postgresql and specified the returned type as TABLE (id uuid, data boolean).

This is the code that I have tried:

BEGIN
    RETURN QUERY SELECT table.id, (table.data <> '') as data FROM table;
END

But it will return NULL for "data" when data is NULL in the table. I was expecting it to return FALSE.

Data column is storing a JSON and I am trying to check if the stored value is not null and not empty

How can I make this code work?

dandelionn
  • 153
  • 2
  • 7

1 Answers1

1

Use is distinct from to use a null-safe comparison:

SELECT table.id, table.data is distinct from '' as data 
FROM table;

Another option is to treat an empty string like null:

SELECT table.id, nullif(table.data, '') is not null as data 
FROM table;
  • I just thought that <> also checks for NULL after reading [this](https://stackoverflow.com/questions/23766084/best-way-to-check-for-empty-or-null-value). Your second solution solved my problem. Thanks. – dandelionn Nov 23 '21 at 14:16