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 postgres-jsonb. For index type selection by data type, see 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. |
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. |
-- 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. |
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 timestampJSON 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. |
-- 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; -- nestedFor the full operator reference, see 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 |
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 tagUUID 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 |
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 overtext. 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 sametimestampcolumn with different session timezones will produce incorrect relative order. Usetimestamptz.now()inside a transaction returns the transaction start time, not the wall clock. Useclock_timestamp()when you need the actual current time.- Array indexes in Postgres start at 1, not 0.
tags[0]always returns NULL. jsonbremoves duplicate keys (last value wins) and does not preserve key insertion order. Usejsononly if you need to round-trip the exact bytes.serialandbigserialare not true types; they expand tointeger DEFAULT nextval(...). PreferGENERATED ALWAYS AS IDENTITYwhich is SQL-standard and avoids sequence ownership quirks.