Data types

The PostgreSQL types you will use most and when to choose each.

Syntax

integer (int4)  bigint (int8)  smallint
numeric(p, s)   real  double precision
text  varchar(n)  char(n)
boolean
date  time  timestamp  timestamptz  interval
uuid  jsonb  json  type[] (arrays)

need	type	notes
counts, ids	`integer` / `bigint`	integer up to about 2.1 billion
money, exact decimals	`numeric(p, s)`	exact; `numeric(12,2)` = up to 10 digits before the point
measurements, science	`double precision`	approximate binary floating point
text	`text`	no length limit; `varchar(n)` only to enforce a maximum
yes/no	`boolean`	TRUE / FALSE / NULL
calendar day	`date`
local date-time	`timestamp`	no time zone
an instant	`timestamptz`	stored as UTC, shown in the session time zone
duration	`interval`	`'2 days 3 hours'`
identifiers	`uuid`	`gen_random_uuid()`, or `uuidv7()` (time-ordered, PostgreSQL 18)
documents	`jsonb`	binary JSON, indexable; `->` returns jsonb, `->>` returns text

char(n) pads with spaces; avoid it.
Integer arithmetic stays integer (5 / 2 = 2).
pg_typeof(expr) tells you the type of any expression.

Examples

Inspect types starter

SELECT pg_typeof(1) AS int_literal, pg_typeof(1.5) AS decimal_literal,
       pg_typeof(now()) AS now_type, pg_typeof(current_date - DATE '2025-01-01') AS date_diff,
       pg_typeof(now() - TIMESTAMP '2025-01-01') AS ts_diff;

1.5 is numeric; date - date is integer; timestamp - timestamp is interval.

Reading JSON stored as text perf

SELECT properties::jsonb ->> 'page' AS page, count(*) AS views
FROM events
WHERE event_type = 'page_view' AND event_id <= 5000
GROUP BY page
ORDER BY views DESC, page
LIMIT 5;

Cast text to jsonb, then ->> extracts a field as text.

Column types from the catalog shop

SELECT column_name, data_type, numeric_precision, numeric_scale, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

information_schema.columns describes every column of every table.

Pitfalls

Money in double precision (0.1 + 0.2 is not exactly 0.3).
Dates stored as text (sorting and arithmetic break).
integer overflow on large sums of large ids or amounts (sum(integer) returns bigint, but integer * integer can overflow).
Mixing timestamp and timestamptz in comparisons.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: bit, datetime2, datetimeoffset, nvarchar(max), decimal(p,s), uniqueidentifier.
MySQL: DATETIME vs TIMESTAMP (converted to UTC), DECIMAL, TINYINT(1) as boolean, JSON.
BigQuery: INT64, NUMERIC / BIGNUMERIC, FLOAT64, STRING, BOOL, DATE, DATETIME, TIMESTAMP, JSON.
Snowflake: NUMBER, FLOAT, VARCHAR, BOOLEAN, DATE, TIMESTAMP_NTZ / _LTZ / _TZ, VARIANT for semi-structured data.
Oracle: NUMBER, VARCHAR2, DATE (includes time), TIMESTAMP; '' is NULL.