What data types does PostgreSQL offer beyond standard SQL?

Question

Accepted Answer

Beyond standard SQL types, PostgreSQL offers a rich set of **specialized data types** — `JSONB`, arrays, `UUID`, ranges, `SERIAL`, and more — that make it especially capable for modern applications storing structured, semi-structured, or specialized data.

## Standard types (with Postgres notes)

```sql
INTEGER, BIGINT, SMALLINT          -- integers
NUMERIC(p,s) / DECIMAL             -- exact decimals (use for money)
REAL, DOUBLE PRECISION             -- floating point
VARCHAR(n), TEXT, CHAR(n)          -- text (TEXT for unlimited)
BOOLEAN                            -- true/false (a real boolean type)
DATE, TIME, TIMESTAMP, TIMESTAMPTZ -- TIMESTAMPTZ = timezone-aware (recommended)
```

## Postgres-specific powerful types

```sql
SERIAL / BIGSERIAL    -- auto-incrementing integer (for primary keys)
UUID                  -- universally unique id (uuid_generate_v4() / gen_random_uuid())

JSONB                 -- binary JSON — queryable, INDEXABLE, efficient (vs slower JSON)
attributes JSONB      -- store flexible/semi-structured data in a relational DB

TEXT[]                -- ARRAYS — a column holding multiple values
tags TEXT[]

INT4RANGE, TSRANGE    -- RANGE types — represent ranges (e.g. a booking time range)
period TSRANGE

INET, CIDR, MACADDR   -- network address types
POINT, POLYGON        -- geometric types (PostGIS extends this for full GIS)
ENUM                  -- custom enumerated types
```

## JSONB and arrays in action

```sql
-- JSONB — store and query structured data
INSERT INTO products (attributes) VALUES ('{"color": "red", "size": "L"}');
SELECT * FROM products WHERE attributes->>'color' = 'red';   -- extract a field
SELECT * FROM products WHERE attributes @> '{"color": "red"}';  -- containment (indexable)

-- arrays
INSERT INTO products (tags) VALUES (ARRAY['new', 'sale']);
SELECT * FROM products WHERE 'sale' = ANY(tags);
```

## Why it matters

PostgreSQL's rich data types are a key reason it's so capable and popular, and understanding them is valuable for leveraging the database effectively.

While standard types (integers, text, dates — with the important note to use **`TIMESTAMPTZ`** for timezone-aware timestamps and **`NUMERIC`** for money) cover basics, Postgres's **specialized types** unlock powerful capabilities: **`JSONB`** (binary, queryable, *indexable* JSON) lets you store flexible, semi-structured data within a relational database — combining relational integrity with NoSQL-like flexibility, which is hugely valuable for modern applications (storing variable attributes, settings, API payloads) and a major Postgres advantage; **arrays** allow multiple values in a column; **`UUID`** suits distributed/non-sequential identifiers; **range types** elegantly represent intervals (booking periods, valid ranges); and specialized types (network addresses, geometric/PostGIS for geospatial, enums) handle domain-specific data.

Understanding these types — especially **JSONB** (one of Postgres's most important features for handling semi-structured data with query and index support) and arrays — lets you choose the right type for your data and exploit Postgres's strengths rather than working around them.

Since the rich type system is central to what makes PostgreSQL powerful (handling both traditional relational and modern semi-structured data needs), understanding the Postgres-specific data types is valuable, practically-relevant knowledge for designing schemas that leverage the database's full capabilities — particularly JSONB for the increasingly common need to store flexible structured data in a robust relational system.