PostgreSQL Error – Value Too Long for Type Character Varying(5)

insertpostgresqlpostgresql-9.4

I created a small development database for practice. It has a table cities with columns cityname and state. The cityname in there is 'Cincinnati', long name right?

mytestdb=# SELECT * FROM cities;
 cityid |  cityname  | state
--------+------------+-------
 12345  | Cincinnati | Ohio
(1 row)

I am unclear as to how and why I am getting this error message upon attempting to add 'San Francisco'.

mytestdb=# INSERT INTO cities VALUES ('San Francisco','CA');
ERROR:  value too long for type character varying(5)

Best Answer

So first, what's the difference..

SELECT x, length(x)
FROM ( VALUES
  ('Cincinnati'),
  ('San Francisco')
) AS t(x);

Here is the output

       x       | length 
---------------+--------
 Cincinnati    |     10
 San Francisco |     13

So..

  1. San Francisco is three characters longer.
  2. They're both over 5 characters.
  3. That can't be the problem.

And further, if Cincinnati was in a varchar(5), it'd have to get truncated.

So the problem is your cityid. It is varchar(5). you probably want that to be an int anyway -- it'll be more compact and faster. So ALTER the table and fix it.

ALTER TABLE cities
  ALTER COLUMN cityid SET DATA TYPE int
  USING cityid::int;

As a side note... maybe someday PostgreSQL will speak column names in error messages. until then at least it's more verbose than SQL Server.

Related Question