Every common PostgreSQL column data type, grouped by family: whole numbers, decimals, text, time, booleans and UUIDs, JSON, network addresses, composite types, and extension types. Each row shows what the type stores and when to choose it.
PostgreSQL has dozens of types, but daily schema design uses a core set. The recurring choices are: integer versus numeric for numbers, varchar versus text for strings (text is usually fine), timestamp versus timestamptz (prefer timestamptz for anything real-world), and json versus jsonb (prefer jsonb, it is binary and indexable). Pick the most specific type that still fits; the database enforces it for you.
Reference table · 46 entries
PostgreSQL Data TypesExplained
46 of 46 rows
Whole numbers
A 2-byte integer from -32768 to 32767.
Rare; only when you must save space.
A 4-byte integer, about plus/minus 2 billion.
The default whole-number column.
An 8-byte integer, about plus/minus 9 quintillion.
Very large counts or external IDs.
An auto-incrementing 4-byte integer.
Legacy auto-numbered keys; since PG 10, GENERATED AS IDENTITY is the recommended replacement.
The 2-byte and 8-byte variants of serial.
Auto-numbering matched to a smallint or bigint column.
An integer the table generates for you (GENERATED ALWAYS AS IDENTITY).
The modern serial; SQL-standard and safer against manual inserts.
Decimals & floats
Arbitrary-precision exact decimals.
Money and anything where rounding errors are unacceptable.
A 4-byte single-precision float.
Approximate science values where precision is not critical.
An 8-byte double-precision float.
Geospatial, statistics, and ML feature values.
A currency amount with a fixed locale.
Seldom; numeric with an app-side currency code is more portable.
Text
Fixed-length text, padded with spaces.
Codes of a known width; rarely needed.
Variable-length text with an optional cap.
Bounded strings such as usernames or emails.
Variable-length text with no length limit.
The general-purpose string column; there is no performance penalty vs varchar.
Time
A calendar date with no time of day.
Birthdays and due dates.
A time of day, with or without a time zone.
Daily alarms or shop hours.
Date and time with no time zone.
Legacy schemas; avoid for new work.
Date and time stored as UTC, shown in the client zone.
The default for any real-world moment; prefer this over timestamp.
A span of time, such as 2 days or 3 hours.
Durations and date arithmetic.
Boolean, UUID & bytes
True or false.
Flags and toggles.
A 128-bit universally unique identifier.
Non-sequential IDs safe to expose (generate in the app or with gen_random_uuid).
Raw binary bytes.
Blobs, images, and opaque payloads; prefer object storage for large files.
Fixed- or variable-length strings of bits.
Packed flags and protocol fields; a single on/off value is still boolean.
JSON & semi-structured
JSON parsed into a binary, indexable form.
Flexible or nested data; the recommended JSON column (supports GIN indexes).
JSON kept as the original text.
Rare; only when you must preserve key order or exact whitespace.
XML text.
Legacy integrations that require XML.
A compiled path expression for navigating JSON data.
Filtering inside jsonb with jsonb_path_query and the SQL/JSON operators.
Network & search
An IPv4 or IPv6 host address.
Storing client or server IPs.
An IP network block, such as 10.0.0.0/24.
Firewall rules and subnet ranges.
A 6-byte or 8-byte (EUI-64) MAC address.
Device and network interface identifiers.
Pre-processed text for full-text search.
Built-in Postgres search before reaching for a separate search engine.
Search terms combined with AND, OR, and prefix operators.
The query half of full-text search, matched against a tsvector with @@.
A write-ahead log position, the log sequence number.
Replication lag and backup monitoring.
Composite & advanced
An array of any base type, e.g. integer[].
Lists of simple values; reach for a child table when items need their own columns.
One value from a fixed list you define.
Status fields with a stable set of options.
A custom row type of several fields.
Repeating field groups shared across tables.
A contiguous range between two bounds.
Schedules, price bands, and version spans, with overlap operators.
A base type wrapped in constraints you define.
Reusable rules such as a non-empty email, enforced at every column that uses it.
An unnamed row of typed values.
PL/pgSQL function returns and intermediate query results.
Built-in two-dimensional geometric values.
Simple plane geometry; real-world locations need PostGIS.
An unsigned 4-byte system object identifier.
Joining system catalogs; not for application columns.
A 63-character internal identifier string.
Catalog queries; regular text for anything user-facing.
Extension types
Key/value string pairs in a single value (extension).
Flat maps; jsonb covers this better today.
A hierarchical label path such as news.tech.ai (extension).
Trees with ancestor and descendant queries.
Text that compares without regard to case (extension).
Case-insensitive uniqueness such as emails, with no lower() index.
Geographic and geometric objects from the PostGIS extension.
Distance, containment, and spatial index queries.
Extension functions that generate UUID values.
Legacy generation; since PG 13, gen_random_uuid() is built in.