skip to content

Text, Numbers & Time

Postgres stores text and varchar(n) alike but blank-pads char(n), keeps timestamptz in UTC and renders it in TimeZone, and offers numeric, float and money. Interviewers probe overflow and time zones.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

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.
open as a page

In PostgreSQL, what does a timestamptz column actually store, and how does the session TimeZone setting change what you read back?

level: juniorimportance: must knowfreq 76%

basics

~10 s

A PostgreSQL timestamptz stores one UTC instant and discards the zone you wrote. Input without an offset is read in the session's TimeZone, and every value is displayed converted to that TimeZone.

open as a page

In PostgreSQL, why can adding interval '1 day' to a timestamptz differ from adding '24 hours', and what does '1 month' do on January 31?

level: middleimportance: should knowfreq 34%

basics

~20 s

A PostgreSQL interval keeps months, days and microseconds apart. Adding '1 day' keeps the local time of day across a DST change while '24 hours' adds elapsed time, and January 31 plus '1 month' clamps to February's last day.

open as a page

In a PostgreSQL transaction, why does now() return the same value on every call, and when do you need clock_timestamp() instead?

level: middleimportance: should knowfreq 52%

basics

~20 s

In PostgreSQL, now() returns the current transaction's start time, so every call in one transaction agrees. clock_timestamp() reads the real clock at each call, which is what you need to time work inside a transaction or statement.

open as a page

For fares in PostgreSQL, why is the money type usually avoided, and what do numeric and double precision trade instead?

level: middleimportance: should knowfreq 46%

basics

~10 s

PostgreSQL's money is an integer of minor units whose decimals and formatting come from lc_monetary, so it cannot carry several currencies safely. Use numeric for exact fares; double precision is fast but inexact.

open as a page

A PostgreSQL fare-ledger insert fails with 'integer out of range' — what overflowed, how do :: casts fix the arithmetic, and what does moving to bigint cost?

level: seniorimportance: should knowfreq 42%

basics

~20 s

PostgreSQL raised SQLSTATE 22003 because a value or an integer-times-integer result passed 2,147,483,647; integers never wrap. Cast an operand with ::bigint so the arithmetic runs in 64 bits; changing the column type rewrites the table and its indexes.

open as a page