From: | Shawn Gennaria <sgennaria2(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | How to determine offending column for insert exceptions |
Date: | 2015-04-21 14:39:32 |
Message-ID: | CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all,
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.
I hope this is possible.
Thanks!
sg
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-04-21 14:59:08 | Re: How to determine offending column for insert exceptions |
Previous Message | Anil Menon | 2015-04-19 15:59:07 | Re: [SQL] function to send email with query results |