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?

Solution 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.

Solution 2

All the datatypes you mention use the same internal representation (moderately famous struct varlena)

The CHAR and VARCHAR datatypes just add length checks to this, and (in case of CHAR), have different space-padding semantics.

You can use TEXT safely wherever nothing of above is important to your logic.

Solution 3

From the page that you linked to:

"There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead."

There doesn't seem to be any drawbacks of using the text data type in Postgres.

However, you should consider if you really want to allow huge texts to be stored in the database. Keeping it as a varchar but with a higher limit would protect you from inadvertently storing vast amounts of data in the database.