skip to content

Column Types

PostgreSQL's types go well past ANSI: timestamptz, numeric and money, collation providers, arrays, ranges, enums and domains. Interviewers check that you pick types on purpose.

part ofPostgreSQLread it →
on this pageshow

explore

questions

17

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 18, how do the libc, ICU and builtin collation providers differ, and which would you pick for a new UTF8 cluster?

level: middleimportance: must knowfreq 50%

basics

~20 s

libc borrows the OS C library's locales; ICU uses the ICU library and allows tailored and nondeterministic collations; builtin (17+) sorts by code point with no external library. A sound pick: builtin default, ICU on columns needing linguistic order.

open as a page

In PostgreSQL, how do you find recipes whose text[] tags column holds one given tag, all of several tags, or any of them?

level: middleimportance: must knowfreq 63%

basics

~20 s

Use 'vegan' = ANY(tags) for one tag, tags @> ARRAY['vegan','quick'] for all of a list, and tags && ARRAY['vegan','quick'] for any of it. Containment and overlap ignore order and duplicates; ANY and ALL follow SQL NULL rules.

open as a page

In PostgreSQL, how do you add a new value to an enum type like order_status, and what can you not do to it afterwards?

level: middleimportance: must knowfreq 56%

basics

~20 s

ALTER TYPE order_status ADD VALUE 'refunded' adds a label, optionally BEFORE or AFTER another, without rewriting any table; inside a transaction block it is unusable until commit. Labels can be renamed but not removed or reordered.

open as a page

After an OS upgrade on a PostgreSQL host, a dictionary app's headwords sort differently and logs warn of a collation version mismatch; what happened, and how do you fix it safely?

level: seniorimportance: must knowfreq 55%

basics

~20 s

The upgrade brought a C library that orders some strings differently, so B-tree indexes built under the old order are now wrong. Rebuild every index depending on the collation, resolve duplicate keys, then run ALTER DATABASE ... REFRESH COLLATION VERSION.

open as a page

In PostgreSQL, how do a database's server encoding and a session's client_encoding differ, and what happens to bytes that do not fit?

level: juniorimportance: should knowfreq 40%

basics

~20 s

The server encoding, normally UTF8, is fixed per database at creation; client_encoding is a per-session setting the server converts to and from. Invalid input bytes fail with SQLSTATE 22021, and characters the other encoding cannot hold fail with 22P05.

open as a page

In PostgreSQL, where do array subscripts start, and what does reading tags[0] or a subscript past the end return?

level: juniorimportance: should knowfreq 46%

basics

~10 s

PostgreSQL array subscripts start at 1 by default. Reading tags[0] or any subscript beyond the bounds returns NULL instead of raising an error, so zero-based off-by-one bugs fail silently.

open as a page

In PostgreSQL, how does a COLLATE clause decide which collation a comparison or ORDER BY uses, and when can an index no longer serve it?

level: middleimportance: should knowfreq 30%

basics

~20 s

An explicit COLLATE beats a column's implicit collation, which beats the default; two different implicit collations in one comparison fail with SQLSTATE 42P22. Each index column has one collation, so a query asking for another cannot use that index.

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

In PostgreSQL 18, how would you make dictionary headword lookups case-insensitive (a lower() index, citext, a nondeterministic ICU collation or casefold()) and what does each cost?

level: seniorimportance: should knowfreq 45%

basics

~20 s

A nondeterministic ICU collation such as und-u-ks-level2 makes equality, UNIQUE and, from 18, LIKE case-insensitive per column; citext lowercases under the database ctype; lower() or 18's casefold() expression indexes keep plain text but need matching queries.

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

In PostgreSQL, what does a domain with a CHECK constraint give you over repeating the CHECK on every column, and what goes wrong when you tighten it on a large schema?

level: seniorimportance: should knowfreq 32%

basics

~20 s

A domain is a named type whose CHECK runs wherever a value is converted to it, so one rule serves every column and function. Tightening it scans every column of that domain under SHARE locks that block writes.

open as a page

In PostgreSQL, why store a shop's opening hours as tstzrange or tstzmultirange rather than opens_at and closes_at columns, and where does a single range run out?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A tstzrange is one value that records its own bound inclusivity, so containment, overlap and adjacency become single operators (@>, &&, -|-) that handle edges and open ends. One range cannot hold a gap; a lunch break needs a tstzmultirange.

open as a page

In PostgreSQL, why store visitor IP addresses as inet rather than text, and when would you use cidr instead?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

inet validates IPv4 and IPv6 input, stores an address with an optional netmask, sorts numerically and supports subnet operators such as <<=. cidr holds only networks and rejects input with bits set right of the mask.

open as a page