Postgres by Example: 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)

-- 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: case-sensitive vs case-insensitive 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).

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    |     5 | |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".

Tip: For case-insensitive text columns, consider the citext extension (CREATE EXTENSION citext) — it lets = and LIKE behave case-insensitively at the column level, so your queries do not have to call LOWER() everywhere.

Try it: Compare lengths: SELECT length('x'::text), length('x'::char(3));. The second is 3 because char(3) pads. Then try SELECT 'foo '::char(6) = 'foo'::char(3); — the answer is true because char comparisons ignore trailing spaces.

Source: text-types.sql

Next: Boolean and Dates

Home: Postgres by Example