Handling PostgreSQL: invalid input syntax for integer: “NULL” while type casting a column declared as varchar into numeric formats
Recently had this (self-imposed) privilege of working with a Postgres table which had columns declared as varchar but they actually had numbers in them. When we started using these columns in our visualization package, it started to break saying that these column contains “NULL”. Strangely, most of the suggested tricks for type casting did not work and finally had it working with a bit of work around. The final SQL query turned out something like below.
SELECT CAST(COALESCE(
(CASE
WHEN CAST(column_A AS TEXT) LIKE ‘%NULL%’ THEN ‘0’
ELSE column_A
END) , ‘0’) AS INTEGER)
FROM table_A
Let me try to explain the nitty-gritty details that happen above..
- Casting the column_A as text which is declared as “Character Varying” at the Postgres end
- Checking if it contains any values as “NULL” in it, if so convert them (COALESCE) into “0”
- Cast the final output values of this column into INTEGER type.
This worked for me finally in converting the column into integer values.
Lessons learnt: Create the table with right datatype defined for the right columns :)
NOTE: If you have any better ways of doing this above process, please do enlighten me in the comments section.
Cheers!
