Re: value too long - but for which column?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Tuesday, October 18, 2011 3:44 PM
To: pgsql-general
Subject: [GENERAL] value too long - but for which column?

Hello,

I see the errors

ERROR: value too long for type character varying(32)
CONTEXT: SQL statement "update pref_users set first_name = $1 , last_name
= $2 , female = $3 , avatar = $4 , city = $5 , last_ip =
$6 , login = now() where id = $7 "
PL/pgSQL function "pref_update_users" line 3 at SQL statement

So is there a way which columns
should be widened and is there
a way to turn string truncation into
a warning instead of a fatal error?

Regards
Alex

--------------------------------/Original Message
----------------------------

The most direct way to determine which field is causing the error is to look
at the data and count characters.

You could rewrite the query to be in the following form:

UPDATE ... SET first_name = ?::varchar(32), last_name = ?::varchar(32),
etc...

When type-casting with an explicit constraint the cast truncates silently
(i.e., without any warnings whatsoever).

The database acts as a last line of defense but you ideally want to push
your data validation logic higher in the stack so you can give meaningful
feedback to the user entering the data. You can use the database metadata
to avoid hard-coding the arbitrary constraints into your software layer.

David J.