PostgreSQL Data Types

Choosing the right PostgreSQL data type for each column is one of the most important decisions you make when designing a schema. The correct type enforces data integrity at the database level, enables efficient indexing, and prevents entire classes of application bugs. PostgreSQL offers a rich set of built-in types covering booleans, text, numbers, dates, JSON, arrays, and more.

Core PostgreSQL data type categories

Boolean: The BOOLEAN type holds TRUE, FALSE, or NULL. PostgreSQL accepts various input literals such as 'yes', 'no', 't', 'f', 1, and 0 and normalizes them.

Character types: Use CHAR(n) for fixed-length, space-padded strings, VARCHAR(n) for variable-length strings up to n characters, and TEXT for unlimited-length strings. In practice TEXT and VARCHAR have identical storage and performance — prefer TEXT unless you need an explicit length constraint.

Numeric types:

• SMALLINT — 2-byte integer, range -32,768 to 32,767.
• INT / INTEGER — 4-byte integer, range -2,147,483,648 to 2,147,483,647.
• BIGINT — 8-byte integer for very large counts or IDs.
• NUMERIC(p,s) / DECIMAL(p,s) — exact arithmetic; ideal for money or scientific measurements.
• REAL / FLOAT4 — 4-byte floating-point (inexact).
• DOUBLE PRECISION / FLOAT8 — 8-byte floating-point (inexact).

Temporal types:

• DATE — calendar date (year, month, day).
• TIME — time of day without time zone.
• TIMESTAMP — date and time without time zone.
• TIMESTAMPTZ — date and time with time zone (stored as UTC); recommended for most applications.
• INTERVAL — a span of time (e.g., INTERVAL '3 days').

UUID: Stores 128-bit Universally Unique Identifiers per RFC 4122. Better uniqueness guarantees than SERIAL and avoids exposing sequential IDs in URLs.

JSON / JSONB: JSON stores plain text JSON re-parsed on each access; JSONB stores a parsed binary form that supports indexing and is faster to query. Prefer JSONB for most use cases.

Arrays: Every data type in PostgreSQL can be used as an array. For example, INTEGER[] or TEXT[] let you store multiple values in a single column.

Practical example: mixed type table

The following CREATE TABLE demonstrates a realistic combination of data types:

CREATE TABLE sensor_readings (
  reading_id  BIGSERIAL PRIMARY KEY,
  device_id   UUID NOT NULL,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  label       VARCHAR(100),
  notes       TEXT,
  temperature NUMERIC(6, 2),
  voltage     DOUBLE PRECISION,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  tags        TEXT[],
  metadata    JSONB
);

Inserting a sample row:

INSERT INTO sensor_readings (device_id, label, temperature, voltage, tags, metadata)
VALUES (
  gen_random_uuid(),
  'Sensor A',
  23.45,
  3.3,
  ARRAY['production', 'zone-1'],
  '{"firmware": "v2.1", "location": "rack-3"}'
)
RETURNING reading_id, recorded_at;

 reading_id |          recorded_at
------------+-------------------------------
          1 | 2026-03-07 14:22:05.123456+00
(1 row)

Type selection tips

• Use TIMESTAMPTZ instead of TIMESTAMP for any column that stores real-world event times — it eliminates ambiguity across time zones.
• Use NUMERIC for financial values where exact arithmetic is required; never use floating-point types for money.
• Prefer TEXT over VARCHAR(n) unless you have a genuine business constraint on length — adding a CHECK constraint is more explicit.
• Use UUID as primary keys when rows are created by distributed clients or when IDs should not be guessable.
• Use JSONB over JSON so you can add GIN indexes and use containment operators like @>.

Reference: PostgreSQL documentation — Data Types.