How do I correct this value too long for type character varying(5)?

Question

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)

Answer 1

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.

Answer 2

The root of the problem is INSERT without target column list
- which is a popular way to shoot yourself in the foot. Only use this syntax shortcut if you know exactly what you are doing.

The manual on INSERT:

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.

The cure: list target columns explicitly.

INSERT INTO cities (cityname, state)  -- target columns!
VALUES ('San Francisco','CA');

This is assuming that cityid can be NULL or has a column default.

Typically, the table should look like this:

CREATE TABLE city  -- personal advice: use singular terms for table names
   city_id  serial PRIMARY KEY
 , cityname text NOT NULL
 , state    text NOT NULL  -- REFERENCES state(state_id)
);

Ideally, you also have a table state listing all possible states and a FOREIGN KEY reference to it.

About serial:

• Safely and cleanly rename tables that use serial primary key columns in Postgres?