PostgreSQL - empty values that are not null and not empty strings

sql postgresql notnull
JasonBK picture JasonBK · Jan 25, 2018 · Viewed 9.4k times · Source

I ran this query on a PostgreSQL table:

select * wkt from table  where column <>'' and  column is not null

..and unexpectedly received several rows with no visible value in that column. Why is this? Is there some 'hidden' value in that column for those rows, or a corrupted table, or something else?

Answer

Vao Tsun picture Vao Tsun · Jan 25, 2018 
t=# select ascii(chr(9));
 ascii
-------
     9
(1 row)

thus

select ascii(column) from table  where column <>'' and  column is not null

should give the idea

https://www.postgresql.org/docs/current/static/functions-string.html

ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.

Related questions

How to drop all NOT NULL constraints from a PostgreSQL table in one go

Is it possible to drop all NOT NULL constraints from a table in one go? I have a big table with a lot of NOT NULL constraints and I'm searching for a solution that is faster than dropping them separately.

Read More
SQL Not Empty instead of Not NULL

I am using postgreSQL. I have a column that: NOT NULL However when I want to insert a row with an empty string as like: '' it doesn't give me an error and accepts. How can I check insert value …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More