skip to content

In PostgreSQL, how do text, varchar(n) and char(n) differ in storage and behaviour, and which should a new column use?

level: juniorimportance: must knowfreq 72%

answer

  1. same storage underneath
  2. the limit is only a check
  3. blank padding and trailing spaces
  4. explicit casts truncate silently
  5. widening a limit skips the rewrite

basics

~10 s

PostgreSQL stores text, varchar(n) and char(n) in the same variable-length format with no speed difference; varchar(n) only adds a length check, and char(n) pads with blanks, making it usually the slowest. Default to text.

solid answer

~40 s

All three share one variable-length representation: a 1-byte header for values up to 126 bytes, a 4-byte header beyond that, with long values compressed and moved out of line. `text` is PostgreSQL's native string type, the one most string functions take; `varchar(n)` behaves like `text` plus a check that the value has at most `n` *characters*, and an over-length `INSERT` fails with SQLSTATE `22001`. `char(n)` blank-pads to `n`, ignores trailing spaces when comparing two `char` values, strips them when cast to `text`, and is usually the slowest of the three because the padding is stored. The manual states there is no other performance difference, so the default is `text`, or `varchar(n)` when `n` is a real business rule; widening that limit later does not rewrite the table.

code

sql · 15 lines
sql
CREATE TABLE flight (
  flight_no   varchar(8) NOT NULL,
  origin_code text       NOT NULL CHECK (char_length(origin_code) = 3),
  remarks     text
);

INSERT INTO flight VALUES ('LH4001-XYZ', 'FRA', NULL);
-- ERROR:  value too long for type character varying(8)

SELECT 'LH4001-XYZ'::varchar(8);              -- LH4001-X  (explicit cast truncates)
SELECT 'FRA  '::char(5) = 'FRA'::char(5);     -- true      (padding ignored)
SELECT octet_length('FRA'::char(5)),           -- 5         (padding is stored)
       length('FRA'::char(5));                 -- 3         (but not counted)

ALTER TABLE flight ALTER COLUMN flight_no TYPE varchar(12);  -- no table rewrite

go deeper

for a junior

Recall that the three types share one storage format, that varchar(n) is text plus a character-count check, and that char(n) pads with blanks. Say text is the sensible default.

for a middle

Explain the 22001 error versus silent truncation on an explicit cast, and why char(n) ignores trailing spaces in comparisons but not in LIKE or regular expressions.

for a senior

Show the migration cost of the choice: widening a varchar limit is free, narrowing or switching to char(n) rewrites the table, so a guessed limit becomes a future outage.

for a principal

Frame the length limit as a data rule owned by the schema, deciding between type-level limits and CHECK constraints by how often the rule is likely to change.

## One storage format, three type names PostgreSQL has three character types in everyday use: **`text`**, **`character varying(n)`** (spelled `varchar(n)`) and **`character(n)`** (spelled `char(n)`, internally `bpchar`, for blank-padded char). Unlike engines where a fixed-width `CHAR` gives fixed-width rows, PostgreSQL stores all three the same way, as a **variable-length value**: - a **1-byte header** plus the bytes of the string for values up to 126 bytes; - a **4-byte header** for anything longer; - long values are compressed and, past a threshold, moved to a side table automatically (that mechanism, TOAST, belongs to the storage internals topic); - the longest storable string is about **1 GB**. The manual's own tip is blunt: there is **no performance difference** among the three, apart from extra storage for the blank-padded type and a few CPU cycles to check the length when storing into a length-constrained column. `char(n)` is *usually the slowest* because the padding is stored and carried around. `text` is the **native** string type: most built-in string functions are declared on `text`, and `varchar` behaves for most purposes like a domain over `text`. ## What varchar(n) enforces The `n` is a constraint on the data, nothing more. It does not reserve space, and a `varchar(255)` value is no larger or smaller than the same value in a `varchar(10000)` or a `text` column. 1. `n` counts **characters, not bytes**, so a multibyte UTF-8 string is measured in characters. 2. `n` must be between 1 and **10,485,760**; `varchar` with no `n` accepts any length. 3. Storing an over-length value with `INSERT` or `UPDATE` raises `value too long for type character varying(n)` (SQLSTATE `22001`, `string_data_right_truncation`) — unless every excess character is a space, in which case the SQL standard requires silent truncation. 4. An **explicit cast** such as `'LH4001-XYZ'::varchar(8)` silently truncates to `n` characters. That is also SQL-standard behaviour, and it surprises people who expect the error. ## char(n): padding and its surprises A `char(n)` value is physically padded with spaces to `n` and stored and displayed that way. Then PostgreSQL works hard to pretend the padding is not there: - trailing spaces are **semantically insignificant** when two `char` values are compared, so `'FRA '::char(5) = 'FRA'::char(5)` is true; - trailing spaces are **removed** when a `char` value is converted to `text` or `varchar`; - but they **are** significant for `LIKE` and regular expressions, which is where padded codes produce missed matches; - `char` with no length means `char(1)`, a classic accident. So `char(n)` buys no speed and adds semantics that differ from the other two types. The common justification elsewhere — fixed-width codes such as three-letter airport codes — is better served in PostgreSQL by `text` with a `CHECK (char_length(code) = 3)`. ## Side by side | | `text` | `varchar(n)` | `char(n)` | |---|---|---|---| | Storage | variable length | variable length | variable length, padded to `n` | | Length rule | none | at most `n` characters | exactly `n` (padded) | | Over-length insert | n/a | error `22001` | error `22001` | | Trailing spaces | significant | significant | ignored in comparison, dropped on cast | | Relative speed | baseline | baseline plus a length check | usually slowest | ## Changing your mind later Type changes are where the choice shows up in production: - **widening** `varchar(8)` to `varchar(12)`, or dropping the limit to `text`, does **not** rewrite the table — the server recognises that every existing value already fits; - **narrowing** the limit, or switching to `char(n)`, is a real type change that rewrites the table and its indexes; - `text` to `varchar` and back, with no collation change, does not rebuild indexes. This is why a guessed `varchar(50)` is a liability: it costs nothing to declare, but the day a value needs 51 characters you are writing a migration. If the limit is a genuine rule, `varchar(n)` documents it in the column type; if it is a guess, use `text`. ## In the flight and fare service For a flight-schedule service: `flight_no varchar(8)` if the carrier code plus number really is capped, `origin_code text` with a length `CHECK`, and `remarks text`. Character set, encoding and collation of those columns are a separate topic (collations and encoding); this choice is only about length and padding.

  • Why might a team choose text with a CHECK constraint over varchar(n) for a length rule?
    A `CHECK (char_length(col) <= n)` expresses the same rule but can say more — a minimum, an exact length, a pattern — and it can be dropped instantly. `varchar(n)` puts the limit in the column type and is cheap to widen, but narrowing it is a type change that rewrites the table. Both enforce characters, not bytes, and both cost the same at run time.
  • In PostgreSQL, what does n count in varchar(n), and what is the largest n allowed?
    `n` counts characters, not bytes, so a UTF-8 name of 20 characters fits `varchar(20)` even if it is 40 bytes. `n` may not exceed 10,485,760; for anything longer, or with no real cap, use `text` or `varchar` without a length, which accept values up to about 1 GB.

  • char(n) is faster in PostgreSQL because it makes rows fixed width.
  • A smaller varchar limit makes each stored value smaller on disk.
  • Inserting an over-length string into varchar(n) silently cuts it to n characters.
  • varchar(n) counts bytes, so UTF-8 text needs a larger n.
  • Raising a varchar(n) limit always rewrites the whole table.