Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)

like image 622
Stephen Lead Avatar asked Apr 08 '16 06:04

Stephen Lead


People also ask

What is character varying data type?

The CHARACTER VARYING data type stores a string of letters, digits, and symbols of varying length, where m is the maximum size of the column (in bytes) and r is the minimum number of bytes reserved for that column.

What is difference between varchar and character varying?

The short answer: there is no difference. The long answer: CHARACTER VARYING is the official type name from the ANSI SQL standard, which all compliant databases are required to support. (SQL compliance Feature ID E021-02.) VARCHAR is a shorter alias which all modern databases also support.

What is default size of character varying in PostgreSQL?

It can store a string up to 65,535 bytes long. In the PostgreSQL Varchar data type i. e. Varchar(n), n is used to denote the character length limit. If n is not specified, it defaults to a character of infinite length.

What is maximum size of text data type in Postgres?

In PostgreSQL, the text data type is used to keep the character of infinite length. And the text data type can hold a string with a maximum length of 65,535 bytes.


2 Answers

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

like image 109
Stephen Lead Avatar answered Oct 13 '22 14:10

Stephen Lead


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.

like image 40
Tometzky Avatar answered Oct 13 '22 14:10

Tometzky