Message-ID: <553665BC.7030400@aklaver.com>
Date: Tue, 21 Apr 2015 07:59:08 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.6.0
MIME-Version: 1.0
To: Shawn Gennaria <sgennaria2@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: How to determine offending column for insert exceptions
References: 
 <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
In-Reply-To: 
 <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 04/21/2015 07:39 AM, Shawn Gennaria wrote:
> 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.

Not sure, but some more information might help:

1) What Postgres version?

2) You mention you are doing this dynamically.
Where is that happening?

In a stored function?
If so what language?

In an external program?

3) How are you handling the exception now?

>
> I hope this is possible.
>
> Thanks!
> sg


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql