I'm attempting to parse a data set of very many columns from numerous CSVs into postgres so I can work with them more easily. To this end, I've created some dynamic queries for table creation, copying from CSVs into a temp table, and then inserting the data to a final table with appropriate data types assigned to each field. The majority of the data can fit into integer fields, but occasionally I hit some entries that need to be text or bigint or floats. Therefore my dynamic queries fail with 'integer out of range' errors and such. Unfortunately, sometimes this happens on a file with thousands of columns, and I'd like to easily figure out which column the erroneous input belongs to without having to manually scour through it. At this point, the data has already been copied into a temp table, so the query producing these errors looks like:
INSERT INTO final_table
SELECT a::int, b::int FROM temp_table
temp_table contains all text fields (since COPY points there and I'd rather not debug at that stage), so I'm trying to coerce them to more appropriate data types with this insert statement.
From this, I'd get an error with SQLSTATE like 22003 and SQLERRM like 'value "2156947514" is out of range for type integer'. I'd like to be able to handle the exception gracefully and modify the data type of the appropriate column, but I don't know how to determine which column contains this data.