34

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?
Kerem
  • 11,377
  • 5
  • 59
  • 58
hemantvsn
  • 1,316
  • 3
  • 12
  • 24

3 Answers3

62

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 6
    As of PG 9.2, `varchar(n)` constraint can be increased/removed without a table rewrite. If you don't want to add SQL constraints directly, it's just easier with an ORM like Django that takes care of length constrained field by mapping them to `varchar(n)`. Is there any other reason to prefer `text` over `varchar(n)` other than the above issue that's addressed in 9.2? http://www.postgresql.org/docs/9.2/static/release-9-2.html – user Jan 02 '15 at 13:01
  • 3
    @buffer: Good point, important update. Postgres 9.2 brought major improvements removing the need for table rewrites when changing the length specifier. But `text` with `CHECK` constraints is still more versatile, more so with the introduction of [`NOT VALID` constraints](http://dba.stackexchange.com/questions/75613/disable-all-constraints-and-table-checks-while-restoring-a-dump/75635#75635) in 9.1 and updates in 9.2. Here is a related blog with a good write-up: http://blog.endpoint.com/2012/11/postgres-alter-column-problems-and.html – Erwin Brandstetter Jan 02 '15 at 14:23
  • @ErwinBrandstetter wouldn't using `char_length()` slightly better then `length()`? – dezso Sep 08 '15 at 14:13
  • @dezso: Both do exactly the same. The only difference: there are variants of `length()` for other data types. – Erwin Brandstetter Sep 08 '15 at 14:20
  • @ErwinBrandstetter apparently, my mind somehow mixed in the `bit_length()` function and `length(string bytea, encoding name )`, too - all into one nonexistent function. – dezso Sep 08 '15 at 14:22
6

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
6

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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 2
    So would using a `CHECK` constraint? – Vérace Oct 24 '17 at 16:42
  • 2
    @Vérace As of [v9.2](https://www.postgresql.org/docs/9.2/release-9-2.html#AEN114949:~:text=Increasing%20the%20length%20limit%20for%20a,no%20longer%20requires%20a%20table%20rewrite) I prefer `varchar(n)` over `text` because it allows me to increase the length without rechecking. Changing a check constraint forces a recheck, regardless of whether you're increasing the char length. That check uses an AccessExclusiveLock, preventing reads, making it non-ideal for production dbs. – DharmaTurtle Aug 16 '20 at 15:54