In PostgreSQL, how do text, varchar(n) and char(n) differ in storage and behaviour, and which should a new column use?
answer
- same storage underneath
- the limit is only a check
- blank padding and trailing spaces
- explicit casts truncate silently
- widening a limit skips the rewrite
basics
~10 sPostgreSQL 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 sAll 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 linesCREATE 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 rewritego deeper
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.
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.
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.
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.