agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Shawn Gennaria <sgennaria2@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to determine offending column for insert exceptions
Date: Tue, 21 Apr 2015 07:59:08 -0700
Message-ID: <553665BC.7030400@aklaver.com> (raw)
In-Reply-To: <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
References: <CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (13+ messages)  latest in thread

Message-ID: <553665BC.7030400@aklaver.com>
Permalink:  ../553665BC.7030400@aklaver.com/
Also on:    postgresql.org/message-id/553665BC.7030400@aklaver.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: adrian.klaver@aklaver.com, sgennaria2@gmail.com
  Subject: Re: How to determine offending column for insert exceptions
  In-Reply-To: <553665BC.7030400@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox