Choose PostgreSQL data types

Use integer or numeric types for numbers, text for unrestricted strings, boolean for true and false, and date or timestamp types for time.

Choose a type from the meaning and operations of the value, not from the format your application happens to receive.

CREATE TABLE invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id uuid NOT NULL,
  total numeric(12, 2) NOT NULL CHECK (total >= 0),
  issued_on date NOT NULL,
  paid_at timestamptz,
  note text
);

integer and bigint are exact whole numbers. numeric is exact decimal arithmetic and fits money or measurements where rounding must be controlled. Floating-point types are faster for scientific approximations, but values such as 0.1 cannot always be represented exactly.

Use date for a calendar day and timestamptz for an instant that may be displayed in different time zones. PostgreSQL stores timestamptz as an absolute instant and converts it for the session time zone. timestamp without a time zone is appropriate for a local wall-clock value only when no global instant is intended.

text and unconstrained varchar have the same practical storage behavior in PostgreSQL. Add a length limit only when it is a real domain rule, not as a guessed optimization.

PostgreSQL also provides UUIDs, arrays, ranges, enums, network types, and JSONB. A specialized type earns its place when database operators, validation, constraints, or indexes simplify real queries. Otherwise, an ordinary column is easier to understand and migrate.

Types are the first integrity boundary. Storing dates as text, booleans as 0 and 1, or money as floating point pushes validation and comparison bugs into every caller.

Try it: design columns for an event with a global start instant, a maximum attendee count, a network address, and a price. Explain which invalid values each chosen type rejects before application code runs.