PostgreSQL data types

Postgres offers rich scalar and structured types. Choosing the right type prevents bugs (timezone-less timestamps) and enables indexing (INET for IPs, JSONB for documents).

Common scalars

CREATE TABLE type_demo (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  weight DOUBLE PRECISION,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  active BOOLEAN NOT NULL DEFAULT true,
  ip INET
);

Practice: Run in psql or DB Fiddle (PostgreSQL). Use your practice database from the workflow lesson.

Text: TEXT vs VARCHAR

Both store strings; performance is similar in modern Postgres. Use TEXT unless you need a documented length cap for validation.

Time types

SELECT
  now() AS server_now,
  CURRENT_DATE AS today,
  '2026-05-28 10:00:00+00'::timestamptz AS explicit_utc;

Prefer TIMESTAMPTZ (timestamp with time zone) for application timestamps—stores UTC internally, displays in session timezone.

Important interview questions and answers

1. Q: NUMERIC vs DOUBLE PRECISION for money?
   A: NUMERIC is exact decimal; floats can round—use NUMERIC for currency.
2. Q: TIMESTAMP vs TIMESTAMPTZ?
   A: TIMESTAMPTZ stores absolute instants; plain TIMESTAMP ignores time zone and causes bugs across regions.

Self-check

1. Which type should store currency amounts?
2. Why avoid TIMESTAMP without time zone for user events?

Tip: Use TIMESTAMPTZ and NUMERIC for production timestamps and money—avoid float currency.