We just released our second eBook: "The Most Important Events to Monitor in Your Postgres Logs"Download Now

U112: Value too long for type

Category: Application / User Errors
SQLSTATE: 22001 (Class 22 — Data Exception: string_data_right_truncation)
Urgency: medium

Example Postgres Log Output:

ERROR: value too long for type character varying(3)
STATEMENT: INSERT INTO my_table(small_column) VALUES ('zzzzz')

Explanation:

This error indicates that you were passing data to a column that is too large to be held by its data type, usually a character or character varying column with a specified length limit.

Depending on how your application handles this, you may loose data in such cases, unless your application saves the data that couldn't be stored elsewhere.

This error can often go unnoticed when testing in development or staging environments, and only becomes a problem once production data is sent to the database.

Recommended Action:

Change the character varying or character column to be a text column type instead.

Sometimes text is not used initially since it is assumed the length limitation improves performance based on experience with other databases. That is not correct for PostgreSQL, using something like character varying (255) actually has a slight CPU cost, since the length needs to be validated at runtime.

You can use the following to change the data type for a column to text:

ALTER TABLE my_table ALTER COLUMN small_column TYPE text;

Since Postgres 9.2 increasing the character limit, including going from varchar to text can be done without a full table rewrite, so this is usually safe to run on production systems.

Learn More:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →