How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

sql postgresql casting
silviot picture silviot · Jan 17, 2010 · Viewed 184.5k times · Source

In PostgreSQL I have a table with a varchar column. The data is supposed to be integers and I need it in integer type in a query. Some values are empty strings. The following:

SELECT myfield::integer FROM mytable

yields ERROR: invalid input syntax for integer: ""

How can I query a cast and have 0 in case of error during the cast in postgres?

Answer

Anthony Briggs picture Anthony Briggs · May 24, 2010

I was just wrestling with a similar problem myself, but didn't want the overhead of a function. I came up with the following query:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres shortcuts its conditionals, so you shouldn't get any non-integers hitting your ::integer cast. It also handles NULL values (they won't match the regexp).

If you want zeros instead of not selecting, then a CASE statement should work:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
Subtracting 1 day from a timestamp date

I am using Datagrip for Postgresql. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). I want to be able to: apply a mathematical operator to subtract 1 day filter it based on a time window …

Read More
Cast syntax to convert a sum to float

Using PostgreSQL 9.3, I want to convert the calculated values to data type float. My first attempt: SELECT float(SUM(Seconds))/-1323 AS Averag; Gives me this error: syntax error at or near "SUM" My second attempt: SELECT to_float(SUM(…

Read More