sql - Any downsides of using data type "text" for storing strings?

Question

According to the PostgreSQL Documentation, they support 3 data-types for character data:

character varying(n), varchar(n)  variable-length with limit
character(n), char(n)             fixed-length, blank padded
text                              variable unlimited length

In my application, I came across few unpleasant scenarios where insert/update queries failed as the desired text to be inserted exceeded the varchar(n) or char(n) limit.

For such cases, changing the data type of such columns to text sufficed.

My questions are:

• If we generalize and change the data type of every character storing column to text, is there any downside in terms of performance/memory?
• If a column with data type text stores 10 or less characters every time, should I go for text or varchar(10)?
• If I go for text what's the downside?

Answer 1

Generally, there is no downside to using text in terms of performance/memory. On the contrary: text is the optimum. Other types have more or less relevant downsides. text is literally the "preferred" type among string types in the Postgres type system, which can affect function or operator type resolution.

In particular, never use char(n) (alias for character(n)), unless you know what you are doing. char or character are just short for character(1), so all the same. The internal name is bpchar (stands fore "blank-padded character"). The type is only there for compatibility with old code and standards. It makes very little sense nowadays, wastes memory and is likely to cause trouble:

• Compare varchar with char
• String field length in Postgres SQL

You can use varchar(n) with length modifier (alias for character varying(n)). But varchar(255) typically indicates a misunderstanding carried over from other RDBMS where it might be a local optimum for performance. In Postgres, the length modifier (255) has no special meaning and rarely makes sense.

• Should I add an arbitrary length limit to VARCHAR columns?

Older versions caused various problems when trying to change the length modifier of varchar(n) later. Most of those have been alleviated in modern Postgres, but text or varchar (alias for character varying) without length specifier (and a CHECK constraint instead) never had any of these issues.

A CHECK constraint is just as fast and less likely to cause troubles with depending views, functions, FK constraints etc. which depend on the column type. And it can do more than just enforce a maximum character length - anything you can put into a boolean expression. See:

• Change PostgreSQL columns used in views

Finally, there is also "char" (with double-quotes): a 1-byte data type for a single ASCII letter used as cheap internal enumeration type.

I rarely use anything but text for character data in Postgres.