Choose PostgreSQL data types

Pick a PostgreSQL type from what the value means and how you will query it, not from the string your API happens to send.

Numbers go in integer or numeric types. Unrestricted text goes in text. True and false go in boolean. Calendar days and instants go in date or timestamp types.

Here is a realistic invoice table that shows the pattern:

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 this on your own project: design columns for an event with a global start instant, a maximum attendee count, a network address, and a price. For each column, name one invalid value the type rejects before application code runs.