一、數(shù)據(jù)類型體系概覽

PostgreSQL 擁有極其豐富的數(shù)據(jù)類型系統(tǒng)，既包含標(biāo)準(zhǔn) SQL 類型，也擴(kuò)展了許多高級類型。

1.1 數(shù)據(jù)類型分類總覽

分類	主要類型	特點(diǎn)	應(yīng)用場景
數(shù)值類型	整數(shù)、小數(shù)、浮點(diǎn)數(shù)	精確/近似計算	統(tǒng)計、財務(wù)、科學(xué)計算
字符串類型	CHAR, VARCHAR, TEXT	文本存儲與處理	用戶信息、文章內(nèi)容
布爾類型	BOOLEAN	邏輯判斷	狀態(tài)標(biāo)志、開關(guān)設(shè)置
日期時間	DATE, TIME, TIMESTAMP	時間處理	日志、事件、計劃
二進(jìn)制數(shù)據(jù)	BYTEA	原始字節(jié)存儲	文件、圖像、加密數(shù)據(jù)
枚舉類型	ENUM	預(yù)定義值集合	狀態(tài)碼、分類標(biāo)簽
幾何類型	POINT, LINE, CIRCLE	空間數(shù)據(jù)	地理位置、圖形計算
網(wǎng)絡(luò)類型	INET, CIDR, MACADDR	網(wǎng)絡(luò)地址	設(shè)備管理、日志分析
JSON 類型	JSON, JSONB	半結(jié)構(gòu)化數(shù)據(jù)	配置、API 數(shù)據(jù)
數(shù)組類型	type[]	同類型集合	標(biāo)簽、多值屬性
范圍類型	數(shù)值/日期范圍	連續(xù)區(qū)間	時間段、價格區(qū)間
特殊類型	UUID, XML, TSVECTOR	特定用途	唯一標(biāo)識、全文搜索

---

二、數(shù)值類型詳解

2.1 整數(shù)類型對比

類型	別名	存儲空間	范圍	描述	推薦場景
smallint	int2	2 字節(jié)	-32,768 到 32,767	小范圍整數(shù)	年齡、數(shù)量較小計數(shù)
integer	int4	4 字節(jié)	-2.1億 到 2.1億	標(biāo)準(zhǔn)整數(shù)	ID、計數(shù)、大多數(shù)數(shù)值
bigint	int8	8 字節(jié)	±9.2×101?	大范圍整數(shù)	大數(shù)據(jù)量、金融金額

2.2 自增序列類型

類型	基礎(chǔ)類型	存儲空間	范圍	描述
smallserial	smallint	2 字節(jié)	1 到 32,767	自增小整數(shù)
serial	integer	4 字節(jié)	1 到 2.1億	自增整數(shù)（推薦）
bigserial	bigint	8 字節(jié)	1 到 9.2×101?	自增大整數(shù)

示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- 自增主鍵
    age SMALLINT,                 -- 年齡
    login_count INTEGER,          -- 登錄次數(shù)
    big_number BIGINT             -- 大數(shù)值
);

2.3 精確小數(shù)類型

類型	別名	存儲空間	精度控制	描述	適用場景
decimal(p,s)	numeric(p,s)	可變	精確	定點(diǎn)小數(shù)	財務(wù)計算、精確測量
numeric(p,s)	decimal(p,s)	可變	精確	定點(diǎn)小數(shù)	同 decimal

精度參數(shù)說明：

• p：總位數(shù)（1-1000+）
• s：小數(shù)位數(shù)

CREATE TABLE financial_data (
    price DECIMAL(10,2),      -- 價格：總共10位，2位小數(shù)
    tax_rate NUMERIC(5,4),    -- 稅率：0.0000 到 0.9999
    amount NUMERIC            -- 任意精度
);

2.4 浮點(diǎn)數(shù)類型

類型	別名	存儲空間	精度	范圍	特點(diǎn)
real	float4	4 字節(jié)	6位	±1.18×10?3? 到 ±3.4×103?	單精度，性能好
double precision	float8	8 字節(jié)	15位	±2.23×10?3?? 到 ±1.79×103??	雙精度，更精確

示例：

CREATE TABLE scientific_data (
    temperature REAL,                    -- 溫度測量
    precise_calculation DOUBLE PRECISION -- 精確計算
);

---

三、字符串與文本類型

3.1 字符串類型對比

類型	描述	存儲空間	特點(diǎn)	推薦場景
char(n)	定長字符串	n 字節(jié)	不足補(bǔ)空格，檢索快	固定長度代碼（如國家代碼）
varchar(n)	變長字符串	實(shí)際長度+1	長度限制，節(jié)省空間	有限長度文本（用戶名、郵箱）
text	變長文本	實(shí)際長度+1	無長度限制，功能強(qiáng)	任意長度文本（內(nèi)容、描述）

3.2 字符串類型選擇指南

場景	推薦類型	理由
固定長度編碼	CHAR(n)	存儲效率高，性能好
有限可變文本	VARCHAR(n)	空間優(yōu)化，有約束
任意長度內(nèi)容	TEXT	無限制，最靈活
不確定長度	TEXT	避免長度限制問題

示例：

CREATE TABLE user_data (
    country_code CHAR(2),        -- 國家代碼，固定2位
    username VARCHAR(50),        -- 用戶名，最多50字符
    email VARCHAR(100),          -- 郵箱地址
    biography TEXT,              -- 個人簡介，無長度限制
    phone CHAR(11)               -- 電話號碼
);

---

四、日期時間類型

4.1 日期時間類型詳解

類型	完整名稱	存儲空間	范圍	精度	示例
date	DATE	4 字節(jié)	4713 BC - 5874897 AD	1 天	'2024-01-15'
time	TIME	8 字節(jié)	00:00:00 - 24:00:00	1 微秒	'14:30:25.123456'
timestamp	TIMESTAMP	8 字節(jié)	4713 BC - 5874897 AD	1 微秒	'2024-01-15 14:30:25'
timestamptz	TIMESTAMP WITH TIME ZONE	8 字節(jié)	4713 BC - 5874897 AD	1 微秒	'2024-01-15 14:30:25+08'
interval	INTERVAL	16 字節(jié)	±178000000 年	1 微秒	'2 days 3 hours'

4.2 日期時間操作示例

-- 創(chuàng)建表
CREATE TABLE events (
    event_date DATE,                          -- 事件日期
    start_time TIME,                          -- 開始時間
    created_at TIMESTAMP,                     -- 創(chuàng)建時間（無時區(qū)）
    updated_at TIMESTAMPTZ DEFAULT NOW(),     -- 更新時間（帶時區(qū)）
    duration INTERVAL                         -- 持續(xù)時間
);

-- 常用函數(shù)
SELECT 
    NOW() AS current_time,                    -- 當(dāng)前時間戳
    CURRENT_DATE AS today,                    -- 當(dāng)前日期
    CURRENT_TIME AS now_time,                 -- 當(dāng)前時間
    EXTRACT(YEAR FROM created_at) AS year,    -- 提取年份
    AGE(created_at) AS time_ago;              -- 計算時間間隔

---

五、布爾與二進(jìn)制類型

5.1 布爾類型

類型	存儲空間	取值	有效輸入
boolean	1 字節(jié)	TRUE, FALSE, NULL	true/false, 't'/'f', 'yes'/'no', '1'/'0'

示例：

CREATE TABLE settings (
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    newsletter_optin BOOLEAN
);

-- 多種寫入方式
INSERT INTO settings VALUES 
    (TRUE, FALSE, NULL),
    ('t', 'f', 'yes'),
    ('1', '0', 'no');

5.2 二進(jìn)制類型

類型	描述	存儲空間	格式	應(yīng)用場景
bytea	二進(jìn)制數(shù)據(jù)	1-4字節(jié)+實(shí)際大小	十六進(jìn)制或轉(zhuǎn)義格式	文件存儲、圖片、加密數(shù)據(jù)

示例：

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    file_name TEXT,
    file_data BYTEA,              -- 存儲二進(jìn)制文件
    file_size INTEGER
);

-- 插入二進(jìn)制數(shù)據(jù)
INSERT INTO documents (file_name, file_data) 
VALUES ('image.png', E'\\x89504E470D0A1A0A0000000D49484452');

---

六、高級數(shù)據(jù)類型

6.1 JSON 類型對比

類型	存儲格式	驗證	索引支持	性能	推薦度
JSON	文本格式	寫入時驗證	有限	讀取慢，寫入快	★★☆☆☆
JSONB	二進(jìn)制格式	寫入時驗證	完整	讀取快，寫入稍慢	★★★★★

JSONB 示例：

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    profile_data JSONB,           -- 推薦使用 JSONB
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入 JSON 數(shù)據(jù)
INSERT INTO user_profiles (profile_data) VALUES 
('{
    "name": "張三",
    "age": 30,
    "address": {
        "city": "北京",
        "street": "朝陽區(qū)"
    },
    "hobbies": ["讀書", "游泳", "編程"]
}');

-- JSON 查詢操作
SELECT 
    profile_data->>'name' AS name,                    -- 獲取字符串值
    profile_data->'address'->>'city' AS city,         -- 嵌套查詢
    profile_data->'hobbies'->>0 AS primary_hobby      -- 數(shù)組元素
FROM user_profiles;

-- JSON 路徑查詢
SELECT profile_data#>>'{address,city}' AS city FROM user_profiles;

6.2 數(shù)組類型

聲明方式	示例	描述
數(shù)據(jù)類型[]	INTEGER[]	整數(shù)數(shù)組
數(shù)據(jù)類型[長度]	VARCHAR(50)[]	字符串?dāng)?shù)組
多維數(shù)組	TEXT[][]	文本二維數(shù)組

數(shù)組操作示例：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],                    -- 標(biāo)簽數(shù)組
    prices DECIMAL[],               -- 價格歷史
    dimensions INTEGER[3]           -- 固定長度數(shù)組
);

-- 插入數(shù)組數(shù)據(jù)
INSERT INTO products (name, tags, prices, dimensions) VALUES 
('筆記本電腦', 
 '{"電子產(chǎn)品","電腦","便攜"}', 
 '{4999.00, 4599.00, 4799.00}', 
 '{30,20,5}');

-- 數(shù)組查詢
SELECT 
    name,
    tags[1] AS primary_tag,         -- 訪問數(shù)組元素
    array_length(tags, 1) AS tag_count,  -- 數(shù)組長度
    unnest(tags) AS individual_tag  -- 展開數(shù)組
FROM products
WHERE '電子產(chǎn)品' = ANY(tags);        -- 數(shù)組包含檢查

6.3 范圍類型

類型	描述	示例	適用場景
int4range	整數(shù)范圍	[1,10)	年齡范圍、數(shù)量區(qū)間
int8range	大整數(shù)范圍	[100,1000]	ID 范圍、大數(shù)值區(qū)間
numrange	數(shù)值范圍	(10.5,20.5]	價格區(qū)間、測量范圍
tsrange	時間戳范圍	[2024-01-01,2024-12-31]	時間段、有效期
daterange	日期范圍	[2024-01-01,2024-06-30]	日期區(qū)間

范圍類型示例：

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    period TSRANGE,                 -- 時間段
    price_range NUMRANGE           -- 價格范圍
);

INSERT INTO reservations (room_id, period, price_range) VALUES 
(1, '[2024-01-15 14:00, 2024-01-20 10:00)', '[100.00, 200.00)');

-- 范圍查詢
SELECT * FROM reservations 
WHERE period @> '2024-01-16 12:00'::TIMESTAMP;  -- 包含時間點(diǎn)

---

七、特殊數(shù)據(jù)類型

7.1 網(wǎng)絡(luò)地址類型

類型	描述	存儲空間	示例	適用場景
inet	IP地址（IPv4/IPv6）	7或19字節(jié)	'192.168.1.1'	設(shè)備IP管理
cidr	網(wǎng)絡(luò)地址塊	7或19字節(jié)	'192.168.1.0/24'	子網(wǎng)劃分
macaddr	MAC地址	6字節(jié)	'08:00:2b:01:02:03'	網(wǎng)絡(luò)設(shè)備

示例：

CREATE TABLE network_devices (
    id SERIAL PRIMARY KEY,
    device_name TEXT,
    ip_address INET,
    network CIDR,
    mac_address MACADDR
);

-- 網(wǎng)絡(luò)操作
SELECT 
    ip_address,
    HOST(ip_address) AS host,           -- 提取主機(jī)部分
    MASKLEN(network) AS mask_length     -- 子網(wǎng)掩碼長度
FROM network_devices
WHERE ip_address << network;            -- IP是否在網(wǎng)段內(nèi)

7.2 其他特殊類型

類型	描述	存儲空間	示例	用途
uuid	通用唯一標(biāo)識符	16字節(jié)	'550e8400-e29b-41d4-a716-446655440000'	分布式ID
xml	XML數(shù)據(jù)	可變	<book><title>SQL</title></book>	結(jié)構(gòu)化文檔
money	貨幣金額	8字節(jié)	'$123.45'	金融數(shù)據(jù)
tsvector	全文搜索向量	可變	'cat:1 dog:2'	全文索引
tsquery	全文搜索查詢	可變	'cat & dog'	搜索查詢

UUID 示例：

-- 啟用 UUID 擴(kuò)展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE distributed_users (
    user_id UUID DEFAULT uuid_generate_v4(),  -- 自動生成 UUID
    username TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO distributed_users (username) VALUES ('john_doe');

---

八、枚舉與幾何類型

8.1 枚舉類型

-- 創(chuàng)建枚舉類型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE product_size AS ENUM ('small', 'medium', 'large', 'x-large');

-- 使用枚舉
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    status user_status DEFAULT 'active'
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    size product_size
);

8.2 幾何類型

類型	描述	存儲空間	示例	用途
point	點(diǎn)坐標(biāo)	16字節(jié)	(3,5)	地理位置
line	無限直線	32字節(jié)	{1,-1,0}	幾何計算
lseg	線段	32字節(jié)	[(1,2),(3,4)]	線段表示
box	矩形	32字節(jié)	(1,1),(0,0)	邊界框
path	路徑	16+16n字節(jié)	[(0,0),(1,1),(2,0)]	運(yùn)動軌跡
polygon	多邊形	40+16n字節(jié)	((0,0),(1,0),(1,1),(0,1))	區(qū)域劃分
circle	圓形	24字節(jié)	<(1,1),2>	圓形區(qū)域

---

九、類型選擇最佳實(shí)踐

9.1 數(shù)據(jù)類型選擇指南

數(shù)據(jù)特征	推薦類型	理由	示例
主鍵標(biāo)識	SERIAL, BIGSERIAL, UUID	自動生成，保證唯一性	用戶ID、訂單ID
金額價格	NUMERIC/DECIMAL	精確計算，避免誤差	商品價格、賬戶余額
一般文本	TEXT	無長度限制，靈活	文章內(nèi)容、描述
固定代碼	CHAR(n)	存儲效率高	國家代碼、狀態(tài)碼
時間戳	TIMESTAMPTZ	時區(qū)支持，標(biāo)準(zhǔn)化	創(chuàng)建時間、更新時間
開關(guān)狀態(tài)	BOOLEAN	語義清晰，存儲高效	是否激活、是否驗證
配置數(shù)據(jù)	JSONB	靈活結(jié)構(gòu)，查詢高效	用戶配置、元數(shù)據(jù)
多個值	數(shù)組類型	避免關(guān)聯(lián)表	標(biāo)簽、分類
唯一標(biāo)識	UUID	分布式唯一性	分布式系統(tǒng)ID
二進(jìn)制文件	BYTEA	原生二進(jìn)制支持	圖片、文檔

9.2 性能優(yōu)化建議

場景	優(yōu)化建議	理由
頻繁查詢的數(shù)值	使用合適范圍的整數(shù)類型	減少存儲，提高計算速度
文本搜索	對 TEXT 列創(chuàng)建索引	加速文本查詢
JSON 查詢	使用 JSONB + GIN 索引	支持各種 JSON 查詢
范圍查詢	使用范圍類型 + GiST 索引	高效的范圍操作
數(shù)組操作	對數(shù)組列創(chuàng)建 GIN 索引	加速數(shù)組包含查詢

---

十、類型轉(zhuǎn)換與函數(shù)

10.1 類型轉(zhuǎn)換方法

-- 顯式類型轉(zhuǎn)換
SELECT '123'::INTEGER;
SELECT CAST('123' AS INTEGER);
SELECT '2024-01-15'::DATE;

-- 隱式類型轉(zhuǎn)換
SELECT 3 + '5';                    -- 自動轉(zhuǎn)為數(shù)字
SELECT 'Hello' || 123;             -- 自動轉(zhuǎn)為文本

-- 類型檢測
SELECT pg_typeof(123.4);           -- 返回數(shù)據(jù)類型

10.2 常用類型函數(shù)

類別	函數(shù)	描述	示例
數(shù)值函數(shù)	ROUND(), CEIL(), FLOOR()	四舍五入、向上/向下取整	ROUND(3.14159, 2) → 3.14
字符串函數(shù)	LENGTH(), UPPER(), LOWER()	長度、大小寫轉(zhuǎn)換	LENGTH('hello') → 5
日期函數(shù)	EXTRACT(), DATE_PART(), AGE()	提取部分、計算間隔	EXTRACT(YEAR FROM NOW())
JSON函數(shù)	->, ->>, jsonb_extract_path()	JSON 數(shù)據(jù)提取	data->>'name'
數(shù)組函數(shù)	array_length(), unnest()	數(shù)組操作	unnest(tags)