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 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?
CodePudding user response:
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;