1. Overall structure of PostgreSQL type system

PostgreSQL's type system is very rich and allows users to define new data types through CREATE TYPE.(PostgreSQL)

The official type system is roughly divided into:

基础类型（Standard SQL Types）
│
├─ 数值类型
├─ 字符类型
├─ 日期时间
├─ 布尔类型
│
Postgres 特有类型
│
├─ JSON / JSONB
├─ Array
├─ Range / Multirange
├─ Geometric
├─ Network Address
├─ UUID
├─ Full-Text Search
├─ XML
│
复杂类型
│
├─ Composite
├─ Domain

One of the design goals of Postgres is to:

Make the database not only relational data, but also support multi-model data structures.

2. Basic data types (Standard SQL Types)

These types generally conform to the SQL standard.

1 Numeric Types

Type	Description
smallint	2-byte integer
integer	4-byte integer
bigint	8-byte integer
numeric(p,s)	arbitrary precision number
real	single precision floating point
double precision	double precision floating point

Example:

CREATE TABLE products (
  id SERIAL,
  price NUMERIC(10,2),
  stock INTEGER
);

Features:

• numeric for financial calculations
• float for scientific computing

2 auto-increment type

Postgres provides:

serial
smallserial
bigserial

Essence:

sequence + default

For example:

id SERIAL

Equivalent:

CREATE SEQUENCE
DEFAULT nextval()

3 string type

Type	Description
char(n)	fixed length
varchar(n)	variable length
text	unlimited length

Recommended practices:

text = varchar

Postgres does not limit text performance.

4 Binary types

bytea

Used for:

• File
• pictures
• binary data

Example:

INSERT INTO files(data)
VALUES (decode('FFD8FFE0','hex'));

5 date and time types

Type	Description
date	date
time	time
timestamp	date time
timestamptz	Time with time zone
interval	time interval

Example:

SELECT now();

Recommended:

timestamptz

Avoid time zone issues.

6 Boolean type

boolean

Value:

true
false
null

Example:

SELECT TRUE;

3. Postgres unique types (special capabilities)

These types are the key to making Postgres a multi-model database.

1 JSON / JSONB type

Postgres provides:

json
jsonb

Difference:

Type	Features
json	text storage
jsonb	Binary parsing storage

jsonb：

• Query faster
• Supports indexes (PostgreSQL)

Example:

CREATE TABLE users (
  id INT,
  profile JSONB
);

Insert:

INSERT INTO users VALUES
(1,'{"name":"tom","age":20}');

Query fields:

SELECT profile->>'name'
FROM users;

JSONB operators:

Operator	Meaning
->	JSON object
->>	text
@>	contains
?	key exists

Example:

SELECT *
FROM users
WHERE profile @> '{"age":20}';

jsonpath query

Postgres supports SQL/JSON Path:

SELECT jsonb_path_query(profile,'$.name')
FROM users;

Support:

wildcard
recursive search
array access

2 Array type

Postgres supports array columns.

CREATE TABLE posts(
  tags TEXT[]
);

Insert:

INSERT INTO posts VALUES
('{react,frontend}');

Access elements:

SELECT tags[1]
FROM posts;

Query array:

SELECT *
FROM posts
WHERE 'react' = ANY(tags);

Array search:

SELECT *
FROM posts
WHERE tags @> ARRAY['react'];

Official tip:

Arrays are not collection models.

3 Range type

Unique to Postgres.

means:

区间

For example:

时间段
数值范围

Built-in range:

int4range
int8range
numrange
tsrange
tstzrange
daterange

Example:

CREATE TABLE booking(
  period TSRANGE
);

Insert:

INSERT INTO booking VALUES
('[2024-01-01,2024-01-10]');

Judgment overlap:

SELECT *
FROM booking
WHERE period && '[2024-01-05,2024-01-07]';

Advantages:

自动处理区间逻辑

4 Multirange

One field represents multiple intervals.

{[1,5),[10,20)}

Used for:

时间碎片
库存时间段

5 UUID types

Unique identifier:

uuid

Example:

SELECT gen_random_uuid();

Advantages:

分布式 ID

6 Network Type

Network address support:

Type
inet
cidr
macaddr

Example:

SELECT inet '192.168.0.1';

Purpose:

IP日志
网络系统

7 Geometry types

Postgres has built-in geometric calculations:

Type
point
line
circle
polygon
box

Example:

SELECT point '(1,2)';

Used for:

简单 GIS

8 Full text search types

Support search:

Type
tsvector
tsquery

Example:

SELECT to_tsvector('postgres is awesome');

Query:

SELECT *
FROM articles
WHERE to_tsvector(content)
@@ to_tsquery('postgres');

9 XML types

xml

Example:

SELECT xpath('/book/title', xmlcol);

10 Domain Type

Domain is:

带约束的类型

Example:

CREATE DOMAIN email AS TEXT
CHECK (VALUE LIKE '%@%');

11 Composite type

Similar to:

struct

Example:

CREATE TYPE address AS (
  city TEXT,
  zip TEXT
);

Use:

CREATE TABLE users(
  addr address
);

4. Advanced query capabilities

Postgres query capabilities are very powerful.

1 CTE（Common Table Expression）

WITH

Example:

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at > now() - interval '7 days'
)
SELECT *
FROM recent_orders;

Advantages:

可读性
模块化SQL

2 Recursive Query

Used for:

树
图
组织架构

Example:

WITH RECURSIVE tree AS (
  SELECT id,parent_id
  FROM category
  WHERE id=1

  UNION ALL

  SELECT c.id,c.parent_id
  FROM category c
  JOIN tree t ON c.parent_id=t.id
)
SELECT * FROM tree;

3 Window Functions

Window functions are a strength of Postgres.

Example:

SELECT
  id,
  salary,
  rank() OVER (ORDER BY salary DESC)
FROM employees;

Common:

rank()
dense_rank()
row_number()
lag()
lead()
sum() OVER

4 LATERAL query

LATERAL allows subqueries to reference the previous table.

SELECT *
FROM users
CROSS JOIN LATERAL
jsonb_each(users.profile);

Commonly used for:

JSON拆解
数组展开

5 JSON query

Postgres JSON queries are very powerful.

Example:

SELECT *
FROM orders
WHERE data->'customer'->>'name' = 'Tom';

Array expansion:

SELECT jsonb_array_elements(items)
FROM orders;

6 ARRAY query

SELECT unnest(tags)
FROM posts;

Expand the array.

7 Range Query

Judgment includes:

SELECT *
FROM booking
WHERE period @> timestamp '2024-01-05';

Judgment overlap:

&&

8 Full Text Search

SELECT *
FROM docs
WHERE to_tsvector(content)
@@ to_tsquery('database & postgres');

5. Postgres type system design ideas

Core idea:

1 Strong type system

Better than MySQL.

For example:

range
json
array
network
geometry

2 Expandable

Users can:

CREATE TYPE

Extensions:

• PostGIS
• pgvector
• citext

3 Multi-model database

Support:

relational
document
graph
search
GIS

6. Summary

Postgres type system + query power = very powerful.

Core competencies:

base type

numeric
string
date/time
boolean
binary

unique type

JSONB
Array
Range
UUID
Network
Geometric
Full Text
XML
Composite
Domain