How to convert "" to NULL in PostgreSQL

Question

I'm with a problem in PostgreSQL. I need to do something like this:

select * from single_occurrences
where 
age::int4 > 29

And I got this error:

ERROR:  invalid input syntax for integer: ""

The field age is a text field. How can I convert all the "" values to NULL values?

Best Regards,

manji · Accepted Answer · 2011-04-05 17:56:42Z

3

SELECT *
  FROM single_occurrences
 WHERE CASE WHEN age="" THEN NULL ELSE age::int4 END > 29

answered Apr 5, 2011 at 17:56

manji

48k5 gold badges98 silver badges104 bronze badges

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

3 Comments

Unless you need to store the age of "3 months" while your at it consider changing the data type to an integer.

Or use interval while you're at it.

Surely those should be single quotes.

bawkstoo · Accepted Answer · 2011-04-05 17:53:24Z

2

UPDATE single_occurrences SET age=NULL WHERE age="";

answered Apr 5, 2011 at 17:53

bawkstoo

3252 gold badges5 silver badges16 bronze badges