Postgresql, select empty fields

Question

I'm trying to get empty "text" fields from my table which I cleared manually with pgadmin. Initially in those fields was '' and I can query them like this:

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

But that not work if I delete text from them and leave cells blank.

How to write query to get those '' and clear cells together in result? Or clear cells alone?

Are the strings empty or NULL? (in postgres '' and NULL are different) — wildplasser
– wildplasser, Commented Jan 5, 2013 at 15:00
I don't know what 'type of blank cell' is this, just deleted all text of cell with pgadmin. Now I try to query WHERE mystr=NULL OR mystr1=NULL, and didn't get result out. — Wine Too
– Wine Too, Commented Jan 5, 2013 at 15:07
You cannot compare to NULL, NULL is not a value, it is orthogonal. You should use WHERE mystr IS NULL instead. — wildplasser
– wildplasser, Commented Jan 5, 2013 at 15:16
Hm, "WHERE mystr='' OR mystr IS NULL OR mystr1='' OR mystr1 IS NULL" still dont find all empty fields. What I do wrong? Should I use some brackets here? Actually, this returns again only cells which have '' inside. — Wine Too
– Wine Too, Commented Jan 5, 2013 at 15:28
Please note that a ' or " is a valid character inside a string. So a string could consist of only one or two quote characters. — wildplasser
– wildplasser, Commented Jan 5, 2013 at 15:39

wildplasser · Accepted Answer · 2013-01-05 15:49:19Z

48

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

Explanation: the coalesce(a,b,c, ...) function traverses the list a,b,c,... from left to right and stops at the first non-null element. a,b,c can be any expression (or constant), but must yield the same type (or be coercable to the same type).

answered Jan 5, 2013 at 15:49

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wine Too Over a year ago

Thank you "wildplasser", never see such expression or command. I have to examine this carefully. This still don't find my empty cells. Luckily, by copying data to notepad I found that in those "empty" cells pgadmin leave one space (chr(32)). So WHERE mystr=' ' give results. Is here (in postgresql) some function like TRIM which can reject spaces from both sides of a string?

wildplasser Over a year ago

Yes, there is a TRIM() function. It is even ANSI-SQL (just like COALESCE() )

Stonetip Over a year ago

In a recent use case, I had to write my WHERE clause like this: WHERE COALESCE(TRIM(parcelid), '') The parcelid field was a VARCHAR(17) and I believe by default the data creators pad the column with spaces.

Collectives™ on Stack Overflow

Postgresql, select empty fields

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related