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.

What is the PostgreSQL Data Types Reference?

Searchable PostgreSQL type reference covering numeric, character, binary, boolean, date/time, JSON, network, geometric and array types — with storage size, value range and common aliases. It runs free in your browser on Gera Tools, with nothing uploaded.

PostgreSQL Data Types Reference

Name: PostgreSQL Data Types Reference
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

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
);

Key decisions that trip people up

json vs jsonb

json stores the original text verbatim — same key order, same whitespace, duplicate keys allowed. jsonb decomposes the JSON into a binary format, removes duplicate keys, and supports GIN indexing and the @>, ?, ?|, ?& operators. In almost all cases jsonb is the correct choice. Use json only if you specifically need to round-trip the exact text (for audit logging, for example).

timestamp vs timestamptz

Plain timestamp stores a date and time with no zone information. If your application runs across time zones or does any date arithmetic, this will silently produce wrong results when daylight saving time shifts the offset. timestamptz (timestamp with time zone) stores the moment as UTC internally and converts to the session TimeZone on retrieval. Default to timestamptz for any real-world datetime; use plain timestamp only for data where the time zone is genuinely irrelevant (a clock face, a local schedule shown always in one zone).

serial vs GENERATED ALWAYS AS IDENTITY

serial is shorthand that creates a sequence and wires it to the column. It is not a real type and has quirks: the sequence is a separate owned object and permissions can drift. The SQL-standard replacement since PostgreSQL 10 is:

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

This is explicit, portable, and avoids the ownership edge cases of serial.

numeric precision

numeric without arguments stores any value at any precision — useful for arbitrary calculations but slower and larger than fixed alternatives. Add (precision, scale) to constrain it:

price  numeric(10,2)   -- up to 8 digits before the decimal, 2 after
ratio  numeric(5,4)    -- four decimal places, e.g. 0.9876

For money, prefer numeric(x,2) over float or real, which are inexact binary representations and accumulate rounding error across arithmetic.

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.
uuid is 16 bytes, always. Consider whether a bigint sequence ID is sufficient before defaulting to UUID — GUIDs make indexes larger and joins slightly slower.