From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | value too long - but for which column? |
Date: | 2011-10-18 19:43:31 |
Message-ID: | CAADeyWjT9+aGGJZTPtRJX+CNx=Ke8wL3=nDaFrXssGYwBUQDmQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-10-18 20:04:00 | Re: value too long - but for which column? |
Previous Message | Viktor Rosenfeld | 2011-10-18 18:56:02 | Re: Use true and false when bulk-exporting boolean data |