When should I use numeric instead of float?

Use numeric (decimal) whenever exactness matters, such as money or quantities, because it stores values precisely. real and double precision are inexact binary floats and will accumulate rounding errors.

What is the difference between json and jsonb?

json stores an exact text copy of the input, preserving whitespace and key order, while jsonb stores a decomposed binary form. jsonb is slightly slower to write but supports indexing and operators, so it is the usual choice.

Should I use timestamp or timestamptz?

Use timestamptz (timestamp with time zone) in almost all cases. It stores the value in UTC and converts to the session time zone on read, avoiding ambiguity. Plain timestamp stores wall-clock time with no zone information.

What does serial actually do?

serial is not a real type — it is shorthand that creates an integer column plus a sequence and a default of nextval(). In modern Postgres, GENERATED ALWAYS AS IDENTITY is the standard-compliant alternative.

Is varchar(n) faster than text?

No. In PostgreSQL there is no performance difference between text and varchar; the length limit on varchar(n) is just a constraint check. Use text unless you specifically want to enforce a maximum length.

PostgreSQL Data Types Reference

Email me this result

Get this tool's output sent to your inbox, plus one useful tool a week. No spam, unsubscribe any time.

Pick the right PostgreSQL type quickly

PostgreSQL has a rich set of built-in data types across numeric, character, date/time, JSON, network, geometric and array categories. This reference lets you search those types by name, alias or range and filter by category, so you can choose the smallest correct type for each column. It runs entirely in your browser.

How it works

Each entry lists the canonical type name, common aliases (e.g. int4 for integer), the category, the storage size and the value range or notes. Fixed-width types like integer and bigint always occupy the same number of bytes, while variable-length types such as text, numeric and jsonb store only the bytes they need plus a small length header. Choose types like this:

CREATE TABLE invoice (
  id        bigserial PRIMARY KEY,
  total     numeric(12,2) NOT NULL,
  paid_at   timestamptz,
  metadata  jsonb
);

Tips and examples

Use bigint/bigserial for identifiers that may exceed two billion rows over the lifetime of a table — switching later is painful.
Index jsonb columns with a GIN index to make containment queries (@>) fast.
numeric without a precision allows arbitrary precision; add (precision, scale) like numeric(12,2) to constrain it.
For network data prefer inet/cidr over text: they validate input and let you query by subnet.