--- title: "Postgres Types Cheatsheet" slug: "postgres-types" category: "cheatsheets" tags: ["cheatsheets", "postgres", "database", "sql", "types", "reference"] status: "stable" last_updated: 2026-05-14 summary: "Postgres column type reference covering text/varchar/char, numeric types, timestamps, json/jsonb, arrays, and uuid with storage notes and casting syntax." related: [ "[[backend/postgres]]", "[[cheatsheets/postgres-functions]]", "[[cheatsheets/postgres-explain]]", "[[cheatsheets/sql-joins]]", "[[backend/postgres-jsonb]]", "[[backend/postgres-indexes]]", "[[comparisons/postgres-vs-mysql]]", "[[cheatsheets/index|Cheatsheets]]", ] --- ## Overview Choosing the right column type affects storage size, index efficiency, and the operators available in queries. This card lists every type worth knowing with storage size, cast syntax, and the key behavioral differences. For JSON querying patterns, see [[backend/postgres-jsonb]]. For index type selection by data type, see [[backend/postgres-indexes]]. ## Text types Prefer `text`; use `varchar(n)` only when the column constraint carries domain meaning. | Type | Max length | Storage | Notes | | ------------ | ------------ | ------------- | ----------------------------------------------------------------------------------------------------------------------------- | | `text` | Unlimited | Variable | Preferred for general strings; no artificial limit. | | `varchar(n)` | n characters | Variable | Identical to `text` with a length check constraint. Use when the column has a meaningful max (e.g., `varchar(255)` for URLs). | | `char(n)` | n characters | Fixed n bytes | Padded with spaces to length n; rarely useful. Use `text` instead. | | `name` | 63 bytes | Fixed | Internal Postgres type for identifiers; not for application data. | | `citext` | Unlimited | Variable | Case-insensitive text; requires `CREATE EXTENSION citext`. | ```sql CREATE TABLE articles ( slug text PRIMARY KEY, -- unbounded; no magic limit title varchar(200) NOT NULL, -- meaningful domain constraint status text CHECK (status IN ('draft', 'stable', 'deprecated')) ); -- Cast between text types SELECT 'hello'::varchar(10); SELECT name::text FROM pg_class LIMIT 5; ``` `varchar(255)` is a MySQL legacy habit. In Postgres there is no performance difference between `varchar(255)` and `text`. ## Numeric types Match the type to the domain; use `numeric` when exact decimal arithmetic matters. | Type | Storage | Range | Notes | | ------------------ | -------- | ------------------------------- | ---------------------------------------------------------------- | | `smallint` | 2 bytes | -32,768 to 32,767 | Rarely needed; use `integer` unless storage is critical. | | `integer` / `int` | 4 bytes | -2,147,483,648 to 2,147,483,647 | Default for most integer columns. | | `bigint` | 8 bytes | ±9.2 × 10^18 | Use for IDs, counts, and any column that might exceed 2 billion. | | `smallserial` | 2 bytes | 1 to 32,767 | Auto-increment shorthand; prefer `identity` columns. | | `serial` | 4 bytes | 1 to 2,147,483,647 | Legacy auto-increment; prefer `GENERATED ALWAYS AS IDENTITY`. | | `bigserial` | 8 bytes | 1 to 9.2 × 10^18 | Legacy big auto-increment. | | `numeric(p,s)` | Variable | Arbitrary precision | Exact arithmetic; required for money, measurements. | | `real` | 4 bytes | ~6 decimal digits | IEEE 754 single-precision float; not exact. | | `double precision` | 8 bytes | ~15 decimal digits | IEEE 754 double; still not exact for base-10 decimals. | ```sql -- Prefer identity over serial CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value numeric(12, 4) NOT NULL -- exact; 8 integer digits, 4 decimal ); -- Cast examples SELECT 42::bigint; SELECT '3.14'::numeric(10,2); ``` Do not store monetary values in `real` or `double precision`; use `numeric` or integer cents. ## Date and time types Always store timestamps with timezone unless the application explicitly handles timezone conversion itself. | Type | Storage | Notes | | ------------- | -------- | ------------------------------------------------------------------------------------------- | | `timestamptz` | 8 bytes | Timestamp with timezone (stored as UTC, displayed in session TZ). Preferred. | | `timestamp` | 8 bytes | No timezone. Use only when the value is always UTC by contract and you control all writers. | | `date` | 4 bytes | Date only; no time component. | | `time` | 8 bytes | Time of day without date or timezone. Rarely used alone. | | `timetz` | 12 bytes | Time with timezone; use `timestamptz` instead. | | `interval` | 16 bytes | Duration; supports arithmetic with timestamps. | ```sql CREATE TABLE sessions ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, started_at timestamptz NOT NULL DEFAULT now(), ended_at timestamptz, duration interval GENERATED ALWAYS AS (ended_at - started_at) STORED ); -- Common operations SELECT now(); -- current timestamptz SELECT now() - INTERVAL '7 days'; -- subtract duration SELECT DATE_TRUNC('month', now()); -- truncate to start of month SELECT EXTRACT(epoch FROM now())::bigint; -- Unix timestamp ``` ## JSON types Prefer `jsonb` for everything except audit logs that must preserve original wire format. | Type | Storage | Indexable | Notes | | ------- | ----------------- | --------- | ---------------------------------------------------------------------------- | | `json` | Text | No | Stores the raw JSON string verbatim. Preserves key order and duplicate keys. | | `jsonb` | Binary decomposed | Yes (GIN) | Parses on insert; faster queries; supports operators. Preferred. | ```sql -- jsonb column with GIN index for key/value search CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, attrs jsonb NOT NULL DEFAULT '{}' ); CREATE INDEX ON products USING GIN (attrs); -- Query operators SELECT * FROM products WHERE attrs @> '{"color": "red"}'; -- contains SELECT attrs->>'color' FROM products; -- extract as text SELECT attrs->'dimensions'->>'width' FROM products; -- nested ``` For the full operator reference, see [[backend/postgres-jsonb]]. ## Arrays Postgres supports native arrays for any base type. | Operation | Syntax | Returns | | -------------- | -------------------------------------- | ------------------- | ----------- | ----- | | Declare | `tags text[]` | Column type | | Literal | `ARRAY['a', 'b']` or `'{a,b}'::text[]` | Array value | | Access element | `tags[1]` | Element (1-indexed) | | Array length | `array_length(tags, 1)` | Integer | | Unnest | `UNNEST(tags)` | Set of rows | | Contains | `tags @> ARRAY['foo']` | Boolean | | Overlap | `tags && ARRAY['foo', 'bar']` | Boolean | | Append | `array_append(tags, 'new')` | Array | | Concatenate | `tags | | ARRAY['x']` | Array | ```sql CREATE TABLE posts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tags text[] NOT NULL DEFAULT '{}' ); CREATE INDEX ON posts USING GIN (tags); INSERT INTO posts (tags) VALUES (ARRAY['postgres', 'sql']); SELECT * FROM posts WHERE tags @> ARRAY['postgres']; SELECT id, UNNEST(tags) AS tag FROM posts; -- one row per tag ``` ## UUID type Use `uuid` for globally unique identifiers rather than `bigint` when rows may be created across distributed systems. | Expression | Result | | ---------------------- | --------------------------------------------------- | | `gen_random_uuid()` | UUID v4 (Postgres 13+; no extension required) | | `uuid_generate_v4()` | UUID v4 via `pgcrypto`; equivalent to above | | `'a0eebc99-...'::uuid` | Cast a string literal | | `uuid_nil()` | The nil UUID `00000000-0000-0000-0000-000000000000` | ```sql CREATE TABLE users ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email text UNIQUE NOT NULL ); ``` UUID v4 primary keys do not sort monotonically, causing B-tree index fragmentation at scale. Use `uuid7` (via extension) or `ULID` when insert rate is high and you care about write performance. ## Common gotchas - `varchar(n)` in Postgres does not improve performance over `text`. The only effect is a constraint. Do not cargo-cult the 255-character limit from MySQL. - `timestamp` (no zone) does not store timezone information. Two different applications writing to the same `timestamp` column with different session timezones will produce incorrect relative order. Use `timestamptz`. - `now()` inside a transaction returns the transaction start time, not the wall clock. Use `clock_timestamp()` when you need the actual current time. - Array indexes in Postgres start at 1, not 0. `tags[0]` always returns NULL. - `jsonb` removes duplicate keys (last value wins) and does not preserve key insertion order. Use `json` only if you need to round-trip the exact bytes. - `serial` and `bigserial` are not true types; they expand to `integer DEFAULT nextval(...)`. Prefer `GENERATED ALWAYS AS IDENTITY` which is SQL-standard and avoids sequence ownership quirks. ## Related - [[backend/postgres]] - [[cheatsheets/postgres-functions]] - [[cheatsheets/postgres-explain]] - [[cheatsheets/sql-joins]] - [[backend/postgres-jsonb]] - [[backend/postgres-indexes]] - [[comparisons/postgres-vs-mysql]] - [[cheatsheets/index|Cheatsheets]]