postgresql, select empty string, COALESCE

Question

To query empty fields I have seen this answer: Postgresql, select empty fields

(unfortunately I don't have enough reputation points to answer @wildplasser on that post, so here we go)

Wildplasser's answer:

SELECT mystr, mystr1 
FROM mytable 
WHERE COALESCE(mystr, '') = '' 
   OR COALESCE(mystr1, '') = ''
    ;

I am not sure I get the COALESCE method, but it also works for me this way (specific for my string data type):

SELECT mystr, mystr1 
FROM mytable 
WHERE mystr = '' ;

My questions are:

Does COALESCE work for any data type?
Is there any better way to query empty strings? i.e., column_value = ' '

404 · Accepted Answer · 2022-07-01 08:40:31Z

First you need to understand the difference between NULL and "empty".

NULL is the absence of a value. Any (or at least almost any) data type can be NULL. When you have a column of type integer, and you don't want to put a value in that field, you put NULL.

"Empty" is a string/text concept. It's a string with an empty value, i.e. ''. A text field with an empty string contains a value: the empty string. It is not the same as containing NULL, i.e. no value. Other data types e.g. integer, boolean, json, whatever, can't have an empty string.

Now to COALESCE. That function works on any data type, and basically it returns the first not-NULL result of its arguments. So COALESCE(NULL, TRUE) returns TRUE because the first argument is NULL; COALESCE(FALSE, TRUE) returns FALSE because the first argument is not NULL; and COALESCE(NULL, NULL) returns NULL because there are no not-NULL arguments.

So, COALESCE(field, '') returns the value of field if it's not NULL, and otherwise returns an empty string. When used in COALESCE(field, '') = '' when trying to find any rows where field is "empty", this is basically saying "if field is NULL then use an empty string in its place, then see if it equals an empty string". This is because NULL and an empty string are not equivalent, and "you" are trying to find any rows where fields are NULL or empty.

In your version of the query, where you just do field = '', that will ONLY return results where field is actually an empty string, not where field is NULL. Which behaviour you desire is up to you.

Daniel E. · Accepted Answer · 2019-02-27 09:34:41Z

2

With COALESCE you will get NULL values too in the first query.

1- In Postgresql, you can't mix datatype example here, but you can use the function to_char to mix values

2- I don't understand your question

answered Feb 27, 2019 at 9:34

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

3 Comments

Javi Over a year ago

I mean if my way of doing it would be efficient for millions of rows, i.e, Is there any better way to query empty strings? i.e., column_value = ' ' ... since I am not an expert in postgresql I am always concerned about creating tables or querying them in an inefficient way

Daniel E. Over a year ago

What do you want to do for Null Values ?

Javi Over a year ago

nothing really, theoretically in my query I will always set/update values with empty or none empty strings

Azka Wahyu · Accepted Answer · 2022-07-01 08:44:33Z

1

I think based on the definition of coalesce itself as "The COALESCE() function returns the first non-null value in a list." means that it work for any data type
I don't really understand the question but i think yes its already the most efficient way to make empty string

answered Jul 1, 2022 at 8:44

Azka Wahyu

111 bronze badge

1 Comment

IT goldman Over a year ago

This has already been answered.

Collectives™ on Stack Overflow

postgresql, select empty string, COALESCE

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related