CODEWITHSUNDEEP
sqlpostgresqltypespostgresql-performance
hemantvsn
hemantvsn

Reputation: 1446

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

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:

Upvotes: 48

Views: 29036

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656864

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:

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.

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:

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.

Upvotes: 79

Guffa
Guffa

Reputation: 700362

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.

Upvotes: 11

Quassnoi
Quassnoi

Reputation: 425401

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.

Upvotes: 6

Related Questions