How to determine which column is implicated in "value too long for type character varying"?

Question

I'm programatically adding data to a PostgreSQL table using Python and psycopg - this is working fine.

Occasionally though, a text value is too long for the containing column, so I get the message:

ERROR: value too long for type character varying(1000)

where the number is the width of the offending column.

Is there a way to determine which column has caused the error? (Aside from comparing each column's length to see whether it is 1000)

Solution 1

Many thanks to @Tometzky, whose comment pointed me in the right direction.

Rather than trying to determine which column caused the problem after the fact, I modified my Python script to ensure that the value was truncated before inserting into the database.

1. access the table's schema using select column_name, data_type, character_maximum_length from information_schema.columns where table_name='test'

2. when building the INSERT statement, use the schema definition to identify character fields and truncate if necessary

Solution 2

I don't think there's an easy way.

I tried to set VERBOSITY in psql, as I assumed this would help, but unfortunately not (on 9.4):

psql
\set VERBOSITY verbose
dbname=>  create temporary table test (t varchar(5));
CREATE TABLE
dbname=> insert into test values ('123456');
ERROR:  22001: value too long for type character varying(5)
LOCATION:  varchar, varchar.c:623

This might be something that warrants discussion on the mailing list, as you are not the only one with this problem.