Обсуждение: 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

and (same error, but different line number)

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

in the log files for PostgreSQL 8.4.7,
but my table has several varchar(32) columns -
which one is it?

pref=> \d pref_users;
                Table "public.pref_users"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       | not null
 first_name | character varying(32)       |
 last_name  | character varying(32)       |
 female     | boolean                     |
 avatar     | character varying(128)      |
 city       | character varying(32)       |
 login      | timestamp without time zone | default now()
 last_ip    | inet                        |
 logout     | timestamp without time zone |
 vip        | timestamp without time zone |
 mail       | character varying(254)      |
Indexes:
    "pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
    TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
    TABLE "pref_hand" CONSTRAINT "pref_hand_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
    TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
    TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
    TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
    TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
    TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
    TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
    TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY
(author) REFERENCES pref_users(id)
    TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
    TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)

And here is my stored procedure
(I apologize for the huge width):

pref=> \df+ pref_update_users


                        List o
f functions
 Schema |       Name        | Result data type |
                                                   Argument data types
                                            |  Type  | Volatility |
Owner | Language |                                 Source code
                         | Descri
ption

--------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------

--------------------------------------------+--------+------------+-------+----------+------------------------------------------------------------------------------+-------
------
 public | pref_update_users | void             | _id character
varying, _first_name character varying, _last_name character varying,
_female boolean, _avatar character vary
ing, _city character varying, _last_ip inet | normal | volatile   |
pref  | plpgsql  |
                         |


              :                 begin


              :


              :                 update pref_users set


              :                     first_name = _first_name,


              :                     last_name  = _last_name,


              :                     female     = _female,


              :                     avatar     = _avatar,


              :                     city       = _city,


              :                     last_ip    = _last_ip,


              :                     login      = now()


              :                 where id = _id;


              :


              :                 if not found then


              :                         insert into pref_users(id,
first_name,


              :                             last_name, female, avatar,
city, last_ip, login)


              :                         values (_id, _first_name,
_last_name,


              :                             _female, _avatar, _city,
_last_ip, now());


              :                 end if;


              :                 end;


              :
(1 row)


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-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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.