Common PostgreSQL Data Types

Choose types based on meaning and required operations. Use INTEGER or BIGINT for whole numbers, NUMERIC(precision, scale) for exact decimal values such as money, REAL or DOUBLE PRECISION for approximate floating-point values, TEXT or VARCHAR for strings, BOOLEAN for true/false values, and DATE, TIME, or TIMESTAMPTZ for temporal values.

For timestamps representing real-world moments, TIMESTAMPTZ is usually a strong default because PostgreSQL stores the instant and displays it in the session time zone. It does not preserve the original named time zone supplied by the client. Avoid storing formatted numbers or dates as strings when they need numeric or temporal behavior.

Table with suitable types

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  in_stock BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

💡 The schema uses an identity column for generated IDs, exact decimal precision for price, a boolean for stock state, and a timestamp with time zone for creation time.

Key Takeaways

Choose types by semantics, not convenience alone.
NUMERIC is appropriate for exact decimal quantities.
TIMESTAMPTZ represents an instant with time-zone-aware input/output behavior.

Knowledge Check

Which type is generally appropriate for exact decimal financial values?

Common PostgreSQL Data Types

STAY UPDATED WITH BYTES!