12 Text Types

PostgreSQL has three character types: text (unbounded), varchar(n) (variable up to n characters), and char(n) (fixed n characters, space-padded). For almost all use cases, text is the right choice. PostgreSQL’s text and varchar have identical performance characteristics — unlike MySQL, there is no benefit to capping the length unless you want the database to enforce a maximum.

What you’ll learn

• The three text types and when each makes sense
• Why text is the default in PostgreSQL
• Length functions: length, char_length, octet_length
• The basics of collation — how text sorts and compares
• Encoding: PostgreSQL stores text in the database’s encoding (usually UTF-8)

Example

-- text: unbounded, recommended default
SELECT 'hello'::text AS t, length('hello'::text) AS chars;

-- varchar(n): same as text but enforces max length
SELECT 'hi'::varchar(10) AS v, length('hi'::varchar(10)) AS chars;

-- char(n): fixed length, padded with spaces
SELECT 'ab'::char(5) AS c, length('ab'::char(5)) AS chars, '|' || 'ab'::char(5) || '|' AS padded;

-- char_length vs octet_length on multibyte text
SELECT char_length('café') AS chars, octet_length('café') AS bytes;

-- A taste of collation: byte-order sort
SELECT * FROM (VALUES ('apple'), ('Banana'), ('cherry')) AS t(name)
ORDER BY name COLLATE "C";

text is the workhorse. It stores strings of any length and is what most application columns should use. Internally, PostgreSQL stores text and varchar the same way — using TOAST (the Oversized Attribute Storage Technique) for large values so the main table stays compact.

varchar(n) is text with a length check. Use it only when you have a real reason to cap length (e.g. integration with a system that requires it). varchar without a length is exactly text.

char(n) is the historical fixed-width type. It pads values with spaces to length n on storage and trims them in some operations — which leads to surprises. Avoid char(n) unless you have a very specific reason (e.g. interfacing with a system that requires fixed-length codes).

Run the example

To run:

$ psql -f source/text-types.sql postgres
   t   | chars
-------+-------
 hello |     5
(1 row)

 v  | chars
----+-------
 hi |     2
(1 row)

   c   | chars |  padded
-------+-------+----------
 ab    |     2 | |ab|
(1 row)
 ...

Common pitfalls

• Choosing varchar(255) from habit: it does nothing useful in PostgreSQL. Use text. If the application needs a length cap, enforce it in the application or with a CHECK constraint.
• char(n) padding silently changes your values. 'foo'::char(10) is stored as 'foo '.
• Multibyte characters: length('café') returns 4 (characters), but octet_length returns 5 (bytes in UTF-8). Match the function to the question you are asking.
• Sorting comparisons depend on the database’s LC_COLLATE. To get byte-order sorting (often faster, and predictable), use COLLATE "C".

Downloads

SQL file: text-types.sql · All lesson files · Setup guide