How to determine which column is associated with "value too large for a character type"?

Question

I programmatically add data to a PostgreSQL table using Python and psycopg - this works fine.

Sometimes, although the text value is too long for the containing column, so I get the message:

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

where the number is the width of the intruder column.

Is there a way to determine which column caused the error? (In addition to comparing each column length to see if it is 1000)

Answer 1

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

Instead of determining which column caused the problem after the fact, I modified my Python script to make sure the value was truncated before being inserted into the database.

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

• when creating an INSERT statement, use a schema definition to identify symbol fields and truncate if necessary

Answer 2

I do not think this is an easy way.

I tried installing VERBOSITY in psql, as I assumed this would help, but unfortunately not (at 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 may be something that requires discussion on the mailing list, since you are not the only one with this problem .