2) Everything is contained in a single stored plpgsql function with multiple transaction blocks to allow me to debug each stage of the process.
3) I'm currently handling exceptions with generic 'WHEN OTHERS THEN' statements to spit out the SQLSTATE and SQLERRM values to help me figure out what's going on. I intend to focus this with statements that catch the particular errors that would arise from trying to incorrectly coerce my text data into other data types.
I'm kind of surprised I haven't been able to find answers to this in google, though I did see someone else asked a similar question on stackoverflow 6 months ago but never got an answer. The best thing I can think of right now is to query pg_attributes to find the column names for the temp_table I'm dealing with and then loop through each one attempting to find a hit on the value that I can see embedded in SQLERRM.
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.
Not sure, but some more information might help:
1) What Postgres version?
2) You mention you are doing this dynamically. Where is that happening?
