Postgres SQL state: 22P02

Question

I need to run the following query in Postgres:

select left(file_date, 10) as date, lob_name, devicesegment, sum(conversion_units::numeric) as units
from bac_search.dash_search_data
where (lob_name= 'Mortgage' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
      or (lob_name= 'Loans' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
group by file_date, lob_name, devicesegment
order by file_date, lob_name, devicesegment;

Despite setting conversion_units to numeric, it is giving me the following error:

ERROR:  invalid input syntax for type numeric: ""
********** Error **********

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02

Of note, I've done some unit testing and when I run this query for Mortgage and delete the line for Loans, it works fine. I've isolated the problem to conversion_units::numeric for Loans. Besides the usual conversion (as I've specified here), I'm not sure what else to try. I read through the questions with this error, but they don't seem to mirror my problem. Any help is appreciated! Thanks!

Answer 1

Apparently conversion_units is a string which can hold values not convertible to numeric.

You immediate problem can be solved this way:

SUM(NULLIF(conversion_units, '')::numeric)

but there can be other values.

You might try to use regexp to match convertible strings:

SUM((CASE WHEN conversion_units ~ E'^\\d(?:\\.\\d)*$' THEN conversion_units END)::numeric)

Answer 2

For future searchers:

I got this error (Postgres SQL state: 22P02) while trying to import data from csv.

with this command:

COPY my_end_table FROM 'D:\check\sample.csv' WITH CSV

Solution: turns out the csv should not contain headers....