Data types overview
PostgreSQL offers many column types, and choosing the right one saves space and prevents mistakes.
In this page:
Syntax
column_name integer
column_name numeric(precision, scale)
column_name text
column_name boolean
column_name timestampData types overview
Common types are integer types (smallint, integer, bigint), numeric(p,s), real and double precision, text and varchar(n), boolean, date, time, timestamp and timestamptz, uuid, json and jsonb, arrays and bytea.
PostgreSQL also supports enums, ranges and custom types. Pick the narrowest type that fits and use numeric for money.
Note:
Prefer timestamptz for moments in time and numeric for currency.
Example: Data types overview
shop=# CREATE TABLE sample (
shop(# id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
shop(# qty integer,
shop(# price numeric(10,2),
shop(# title text,
shop(# active boolean,
shop(# created timestamptz DEFAULT now(),
shop(# attrs jsonb,
shop(# tags text[]
shop(# );
CREATE TABLE⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Using float for money
- Storing dates as text
- Using varchar(255) everywhere without a reason
Chapter Summary
- Integers: smallint, integer, bigint
- Text: text and varchar(n)
- Time: date, timestamp, timestamptz
- Also boolean, numeric, uuid, jsonb, arrays
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: