PostgreSQL Data Types

PostgreSQL Data Types

Choosing the right column type is one of the most impactful decisions you make when designing a schema. Postgres provides a rich built-in type system that goes far beyond what most other databases offer.

Text Types

• text — variable-length string, no practical length limit. Prefer this over varchar(n) unless you have a specific length constraint to enforce.
• varchar(n) — variable-length string with a hard maximum of n characters. Useful for fields like ISO country codes where the limit is meaningful.
• char(n) — fixed-length, right-padded with spaces. Rarely used in modern schemas.

Numeric Types

• smallint — 2 bytes, range -32 768 to 32 767.
• integer / int — 4 bytes, range ±2.1 billion. The everyday integer.
• bigint — 8 bytes, range ±9.2 quintillion. Use for IDs in high-volume tables.
• numeric(p,s) / decimal(p,s) — exact fixed-point arithmetic. Always use this for money — never float.
• real / double precision — floating-point. Fine for scientific calculations, not money.

Auto-Increment Types

• serial — shorthand for integer backed by an auto-generated sequence. Legacy but widely seen.
• bigserial — same but backed by bigint.
• GENERATED ALWAYS AS IDENTITY — the modern SQL-standard replacement for serial. Prefer this in new schemas.

Date & Time Types

• date — calendar date only (year, month, day).
• time — time of day without timezone.
• timestamp — date + time, no timezone. Stores local wall-clock time — be careful.
• timestamptz — date + time with timezone. Postgres stores internally as UTC and converts on output. Use this for almost everything.
• interval — a duration, e.g. '2 hours 30 minutes'::interval.

Other Important Types

• boolean — true / false / NULL. Accepts 't', 'yes', '1' as input.
• uuid — 128-bit universally unique identifier. Store as uuid, not as text.
• jsonb — binary JSON. Supports indexing and operators. Prefer over json for queries.
• json — stores raw JSON text. Slightly faster for write-once data you only read back whole.
• bytea — raw binary data (images, files). Consider object storage instead for large blobs.
• Arrays — any type can be made an array, e.g. text[], integer[].

-- Demonstrating type casting
SELECT
  '2024-01-15'::date,
  NOW()::timestamptz,
  '3.14'::numeric(10,2),
  gen_random_uuid()::uuid;