PostgreSQL 数据类型

简介

PostgreSQL 提供了一套全面的数据类型，用于存储和操作各种类型的数据。理解这些数据类型对于设计高效的数据库架构和编写正确的 SQL 查询至关重要。

数据类型分类

PostgreSQL 数据类型分为以下几类：

数值类型 - 整数、浮点数、精确数字
字符类型 - 字符串、文本
日期/时间类型 - 日期、时间、时间戳
布尔类型 - 真/假值
二进制数据类型 - 二进制字符串
JSON 类型 - 结构化数据
UUID 类型 - 通用唯一标识符
数组类型 - 元素数组
枚举类型 - 自定义枚举值
几何类型 - 点、线、形状
网络地址类型 - IP 地址、MAC 地址
范围类型 - 值范围

数值类型

整数类型

类型	存储	范围	描述
SMALLINT	2 字节	-32,768 到 32,767	小整数
INTEGER (INT)	4 字节	-2,147,483,648 到 2,147,483,647	标准整数
BIGINT	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	大整数

SMALLINT 示例

sql

CREATE TABLE products (
    id SMALLSERIAL PRIMARY KEY,
    quantity SMALLINT NOT NULL,
    min_stock SMALLINT DEFAULT 10
);

INSERT INTO products (quantity) VALUES (100), (500), (32767);

INTEGER 示例

sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    post_count INTEGER DEFAULT 0,
    followers INTEGER DEFAULT 0
);

INSERT INTO users (post_count, followers) VALUES 
    (50, 1500),
    (1000000, 5000000);

BIGINT 示例

sql

CREATE TABLE statistics (
    id BIGSERIAL PRIMARY KEY,
    population BIGINT,
    national_debt BIGINT,
    page_views BIGINT
);

INSERT INTO statistics (population, national_debt, page_views) VALUES
    (330000000, 28000000000000, 999999999999999);

序列类型（自增）

序列类型是 PostgreSQL 特有的自增类型：

类型	存储	范围	等同于
SMALLSERIAL	2 字节	1 到 32,767	带序列的 SMALLINT
SERIAL	4 字节	1 到 2,147,483,647	带序列的 INTEGER
BIGSERIAL	8 字节	1 到 9,223,372,036,854,775,807	带序列的 BIGINT

sql

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE NOT NULL
);

-- SERIAL 自动创建序列
-- 获取下一个值
SELECT nextval('orders_order_id_seq');

-- 设置序列到特定值
SELECT setval('orders_order_id_seq', 1000);

-- 获取当前值
SELECT currval('orders_order_id_seq');

浮点类型

类型	存储	精度	范围
REAL	4 字节	6 位小数	1E-37 到 1E+37
DOUBLE PRECISION	8 字节	15 位小数	1E-307 到 1E+308

sql

CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    temperature REAL,
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    pi_value DOUBLE PRECISION DEFAULT 3.141592653589793
);

INSERT INTO measurements (temperature, latitude, longitude) VALUES
    (98.6, 37.7749, -122.4194),
    (-12.5, 40.7128, -74.0060);

精确数值类型

NUMERIC 和 DECIMAL 是具有用户指定精度的精确数值类型。

sql

-- NUMERIC(precision, scale)
-- precision: 总位数
-- scale: 小数点后位数

CREATE TABLE financial (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),           -- 总共 10 位，小数点后 2 位
    quantity NUMERIC(5),            -- 5 位，无小数
    exchange_rate NUMERIC(10, 6),   -- 10 位，小数点后 6 位
    balance DECIMAL(15, 2)          -- DECIMAL 是 NUMERIC 的别名
);

INSERT INTO financial (price, quantity, exchange_rate, balance) VALUES
    (19.99, 100, 1.234567, 10000.50),
    (999.99, 5, 0.123456, 50000.00);

-- 精度示例
SELECT 
    NUMERIC '123.45' AS exact_number,
    NUMERIC(10, 2) '123.4567' AS rounded,  -- 变为 123.46
    NUMERIC '9999999999' AS large_number;

货币类型

MONEY 类型存储具有固定小数精度的货币金额。

sql

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount MONEY,
    balance MONEY
);

INSERT INTO transactions (amount, balance) VALUES
    ('$19.99', '$1,234.56'),
    (19.99, 1234.56);

SELECT 
    amount,
    balance,
    amount + balance AS total
FROM transactions;

注意：MONEY 类型依赖于区域设置。对于国际应用程序，请使用 NUMERIC。

字符类型

类型	描述	存储
CHAR(n)	固定长度字符串	n 字节
VARCHAR(n)	可变长度有限制	实际长度 + 1 字节
TEXT	可变无限长度	实际长度 + 1 字节

CHAR 示例

sql

CREATE TABLE codes (
    country_code CHAR(2),      -- 始终 2 个字符
    state_code CHAR(2),
    zip_code CHAR(5)
);

INSERT INTO codes VALUES ('US', 'CA', '94102');

VARCHAR 示例

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    bio VARCHAR(500)
);

INSERT INTO users (username, email, bio) VALUES
    ('john_doe', 'john@example.com', '软件开发者'),
    ('jane_smith', 'jane@example.com', NULL);

TEXT 示例

sql

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    summary TEXT
);

INSERT INTO articles (title, content) VALUES
    ('PostgreSQL 指南', '这是一篇很长的文章内容...');

最佳实践：对于可变长度字符串，使用 TEXT，除非需要强制最大长度。

布尔类型

BOOLEAN 类型存储真/假值。

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    has_premium BOOLEAN
);

-- 插入布尔值的各种方式
INSERT INTO users (username, is_active, is_verified, has_premium) VALUES
    ('user1', TRUE, FALSE, NULL),
    ('user2', 't', 'f', 'yes'),
    ('user3', 'true', 'false', 'no'),
    ('user4', '1', '0', 'y');

-- 查询布尔值
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_verified;  -- 等同于 is_verified = TRUE
SELECT * FROM users WHERE NOT is_active;

布尔值表示：

TRUE：TRUE、't'、'true'、'y'、'yes'、'on'、'1'
FALSE：FALSE、'f'、'false'、'n'、'no'、'off'、'0'

日期/时间类型

类型	存储	描述	范围
DATE	4 字节	仅日期	公元前 4713 年到公元 5874897 年
TIME	8 字节	一天中的时间	00:00:00 到 24:00:00
TIMESTAMP	8 字节	日期和时间	公元前 4713 年到公元 294276 年
TIMESTAMPTZ	8 字节	带时区的时间戳	公元前 4713 年到公元 294276 年
INTERVAL	16 字节	时间间隔	-178000000 年到 178000000 年

DATE 示例

sql

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE NOT NULL,
    created_at DATE DEFAULT CURRENT_DATE
);

INSERT INTO events (event_name, event_date) VALUES
    ('会议', '2024-06-15'),
    ('研讨会', DATE '2024-07-20');

-- 日期操作
SELECT 
    event_date,
    event_date + INTERVAL '7 days' AS one_week_later,
    CURRENT_DATE - event_date AS days_ago
FROM events;

TIME 示例

sql

CREATE TABLE schedules (
    id SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

INSERT INTO schedules (task_name, start_time, end_time) VALUES
    ('晨会', '09:00:00', '10:00:00'),
    ('午休', TIME '12:00', TIME '13:00');

TIMESTAMP 示例

sql

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

INSERT INTO logs (message) VALUES ('系统启动');

-- 时间戳操作
SELECT 
    created_at,
    created_at + INTERVAL '1 hour' AS one_hour_later,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day
FROM logs;

TIMESTAMPTZ 示例

sql

CREATE TABLE user_actions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(50),
    action_time TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO user_actions (user_id, action) VALUES (1, 'login');

-- 时区操作
SELECT 
    action_time,
    action_time AT TIME ZONE 'UTC' AS utc_time,
    action_time AT TIME ZONE 'Asia/Shanghai' AS shanghai_time
FROM user_actions;

最佳实践：始终使用 TIMESTAMPTZ 存储时间戳以正确处理时区转换。

INTERVAL 示例

sql

SELECT 
    INTERVAL '1 day' AS one_day,
    INTERVAL '2 hours 30 minutes' AS duration,
    INTERVAL '1 year 2 months 3 days' AS period,
    NOW() + INTERVAL '7 days' AS next_week;

-- 间隔计算
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    start_date TIMESTAMP,
    duration INTERVAL,
    end_date TIMESTAMP GENERATED ALWAYS AS (start_date + duration) STORED
);

INSERT INTO subscriptions (user_id, start_date, duration) VALUES
    (1, NOW(), INTERVAL '1 month'),
    (2, NOW(), INTERVAL '1 year');

二进制数据类型

类型	存储	描述
BYTEA	可变	二进制数据（字节数组）

sql

CREATE TABLE files (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255),
    file_data BYTEA,
    file_size INTEGER
);

-- 插入二进制数据
INSERT INTO files (filename, file_data) VALUES
    ('image.png', '\xDEADBEEF'::bytea),
    ('document.pdf', decode('48656C6C6F', 'hex'));

-- 查询二进制数据
SELECT 
    filename,
    encode(file_data, 'hex') AS hex_data,
    encode(file_data, 'base64') AS base64_data,
    octet_length(file_data) AS size_bytes
FROM files;

JSON 类型

PostgreSQL 支持两种 JSON 数据类型：JSON 和 JSONB（二进制 JSON）。

类型	存储	描述	索引
JSON	可变	基于文本的 JSON	否
JSONB	可变	二进制 JSON	是（GIN 索引）

推荐：使用 JSONB 以获得更好的性能和索引能力。

JSON 示例

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
    ('笔记本电脑', '{"brand": "Dell", "ram": "16GB", "storage": "512GB"}'),
    ('手机', '{"brand": "Apple", "model": "iPhone 14", "color": "black"}');

-- 查询 JSON 数据
SELECT 
    name,
    attributes->>'brand' AS brand,
    attributes->>'ram' AS ram
FROM products;

JSONB 示例

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    profile JSONB
);

INSERT INTO users (username, profile) VALUES
    ('john', '{"age": 30, "city": "北京", "hobbies": ["阅读", "游戏"]}'),
    ('jane', '{"age": 25, "city": "上海", "hobbies": ["旅行", "摄影"]}');

-- JSONB 查询
SELECT * FROM users WHERE profile->>'city' = '北京';
SELECT * FROM users WHERE profile @> '{"age": 30}';
SELECT * FROM users WHERE profile ? 'hobbies';

-- 创建 GIN 索引以提高性能
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 更新 JSONB
UPDATE users 
SET profile = profile || '{"verified": true}'
WHERE username = 'john';

UUID 类型

UUID（通用唯一标识符）是 128 位标识符。

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO sessions (user_id) VALUES (1), (2);

SELECT * FROM sessions;
-- 输出：session_id 类似 '550e8400-e29b-41d4-a716-446655440000'

数组类型

PostgreSQL 允许将列定义为任何内置或用户定义类型的数组。

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    prices NUMERIC(10,2)[],
    dimensions INTEGER[]
);

-- 插入数组
INSERT INTO products (name, tags, prices, dimensions) VALUES
    ('笔记本电脑', ARRAY['电子产品', '计算机'], ARRAY[999.99, 1299.99], ARRAY[15, 10, 1]),
    ('书籍', '{"小说", "畅销书"}', '{9.99, 12.99}', '{8, 6, 1}');

-- 查询数组
SELECT * FROM products WHERE '电子产品' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['计算机'];

-- 数组函数
SELECT 
    name,
    array_length(tags, 1) AS tag_count,
    tags[1] AS first_tag,
    array_append(tags, 'new') AS updated_tags
FROM products;

枚举类型

枚举类型是具有静态、有序值集的自定义数据类型。

sql

-- 创建枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20),
    status order_status DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (order_number, status) VALUES
    ('ORD-001', 'pending'),
    ('ORD-002', 'processing'),
    ('ORD-003', 'shipped');

-- 使用枚举查询
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status > 'pending';  -- 有序比较

-- 列出所有枚举值
SELECT enum_range(NULL::order_status);

几何类型

PostgreSQL 支持用于 2D 空间数据的各种几何类型。

几何数据类型表示二维的平面物体。

下表列出了 PostgreSQL 支持的几何类型。

最基本的类型：点。它是其它类型的基础。

名字	存储空间	说明	表现形式
point	16 字节	平面中的点	(x,y)
line	32 字节	(无穷)直线(未完全实现)	((x1,y1),(x2,y2))
lseg	32 字节	(有限)线段	((x1,y1),(x2,y2))
box	32 字节	矩形	((x1,y1),(x2,y2))
path	16+16n 字节	闭合路径(与多边形类似)	((x1,y1),...)
path	16+16n 字节	开放路径	[(x1,y1),...]
polygon	40+16n 字节	多边形(与闭合路径相似)	((x1,y1),...)
circle	24 字节	圆	<(x,y),r> (圆心和半径)

sql

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position POINT,
    area BOX,
    coverage CIRCLE
);

INSERT INTO locations (name, position, area, coverage) VALUES
    ('商店 A', POINT(10, 20), BOX(POINT(0,0), POINT(100,100)), CIRCLE(POINT(50,50), 25)),
    ('商店 B', '(30, 40)', '((0,0),(50,50))', '<(25,25),15>');

-- 几何操作
SELECT 
    name,
    position,
    position <-> POINT(0,0) AS distance_from_origin,
    area @> POINT(50,50) AS contains_point
FROM locations;

网络地址类型

PostgreSQL 提供用于存储网络地址的数据类型。

类型	存储	描述
INET	7 或 19 字节	IPv4 或 IPv6 主机地址
CIDR	7 或 19 字节	IPv4 或 IPv6 网络
MACADDR	6 字节	MAC 地址
MACADDR8	8 字节	MAC 地址（EUI-64 格式）

sql

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

INSERT INTO network_devices (device_name, ip_address, network, mac_address) VALUES
    ('服务器 1', '192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03'),
    ('路由器', '10.0.0.1', '10.0.0.0/8', '08-00-2b-01-02-03'),
    ('笔记本', '2001:db8::1', '2001:db8::/32', '08:00:2b:01:02:03');

-- 网络查询
SELECT * FROM network_devices WHERE ip_address << '192.168.1.0/24';
SELECT * FROM network_devices WHERE network >>= '192.168.1.100';

范围类型

范围类型表示某个元素类型的值范围。

类型	描述
INT4RANGE	INTEGER 范围
INT8RANGE	BIGINT 范围
NUMRANGE	NUMERIC 范围
TSRANGE	TIMESTAMP 范围
TSTZRANGE	TIMESTAMPTZ 范围
DATERANGE	DATE 范围

sql

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    guest_name VARCHAR(100),
    stay_period DATERANGE,
    price_range NUMRANGE
);

INSERT INTO reservations (room_id, guest_name, stay_period, price_range) VALUES
    (101, '张三', '[2024-06-01,2024-06-05)', '[100,150)'),
    (102, '李四', DATERANGE('2024-06-10', '2024-06-15'), NUMRANGE(150, 200));

-- 范围查询
SELECT * FROM reservations WHERE stay_period @> '2024-06-03'::DATE;
SELECT * FROM reservations WHERE stay_period && '[2024-06-01,2024-06-10)'::DATERANGE;

-- 范围操作
SELECT 
    guest_name,
    lower(stay_period) AS check_in,
    upper(stay_period) AS check_out,
    upper(stay_period) - lower(stay_period) AS nights
FROM reservations;

复合类型

您可以创建自定义复合类型（类似于结构体）。

sql

-- 创建复合类型
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(2),
    zip_code VARCHAR(10)
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    home_address address,
    work_address address
);

INSERT INTO customers (name, home_address, work_address) VALUES
    ('张三', 
     ROW('主街 123 号', '北京', 'BJ', '100001'),
     ROW('办公大道 456 号', '北京', 'BJ', '100002'));

-- 查询复合类型
SELECT 
    name,
    (home_address).city AS home_city,
    (work_address).city AS work_city
FROM customers;

XML 类型

PostgreSQL 支持存储和查询 XML 数据。

sql

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    content XML
);

INSERT INTO documents (title, content) VALUES
    ('文档 1', '<doc><title>你好</title><body>世界</body></doc>'),
    ('文档 2', XMLPARSE(DOCUMENT '<doc><title>测试</title></doc>'));

-- 查询 XML
SELECT 
    title,
    xpath('/doc/title/text()', content) AS xml_title
FROM documents;

位串类型

位串是由 1 和 0 组成的字符串。

类型	描述
BIT(n)	固定长度位串
BIT VARYING(n)	可变长度位串

sql

CREATE TABLE flags (
    id SERIAL PRIMARY KEY,
    permissions BIT(8),
    features BIT VARYING(16)
);

INSERT INTO flags (permissions, features) VALUES
    (B'11110000', B'101010'),
    ('10101010', '11111111');

-- 位操作
SELECT 
    permissions,
    permissions & B'11110000' AS masked,
    permissions | B'00001111' AS combined
FROM flags;

数据类型转换

PostgreSQL 提供了几种在数据类型之间转换的方法。

CAST 函数

sql

SELECT 
    CAST('123' AS INTEGER) AS int_value,
    CAST('2024-01-01' AS DATE) AS date_value,
    CAST(123.45 AS INTEGER) AS truncated,
    CAST('true' AS BOOLEAN) AS bool_value;

:: 操作符（PostgreSQL 特有）

sql

SELECT 
    '123'::INTEGER AS int_value,
    '2024-01-01'::DATE AS date_value,
    123.45::INTEGER AS truncated,
    'true'::BOOLEAN AS bool_value;

转换函数

sql

-- 字符串转数字
SELECT 
    to_number('1,234.56', '9,999.99') AS number_value,
    to_date('2024-01-01', 'YYYY-MM-DD') AS date_value,
    to_timestamp('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;

-- 数字转字符串
SELECT 
    to_char(1234.56, '9,999.99') AS formatted_number,
    to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;

选择正确的数据类型

最佳实践

使用适当的数值类型
- 对大多数整数使用 INTEGER
- 仅在需要时使用 BIGINT（节省空间）
- 对精确的十进制计算使用 NUMERIC（货币、测量）
- 避免对财务数据使用 REAL/DOUBLE PRECISION
字符串类型
- 对无限制的可变长度字符串使用 TEXT
- 仅在需要强制最大长度时使用 VARCHAR(n)
- 除非需要固定长度字符串，否则避免使用 CHAR(n)
日期/时间类型
- 始终对时间戳使用 TIMESTAMPTZ（处理时区）
- 对不带时间的日期使用 DATE
- 对持续时间使用 INTERVAL
布尔值 vs 整数
- 对真/假值使用 BOOLEAN（意图更清晰）
- 不要对布尔逻辑使用 INTEGER（0/1）
JSON 类型
- 优先使用 JSONB 而不是 JSON（更好的性能、索引）
- 对半结构化数据使用 JSONB
数组 vs 单独的表
- 对不需要查询的简单列表使用数组
- 对复杂关系使用单独的表

性能考虑

sql

-- 好：适当的类型
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- 自增
    username VARCHAR(50) NOT NULL,      -- 限制长度
    email VARCHAR(255) NOT NULL,        -- 标准邮箱长度
    age SMALLINT,                       -- 小数字
    balance NUMERIC(10,2),              -- 精确小数
    is_active BOOLEAN DEFAULT TRUE,     -- 清晰的布尔值
    created_at TIMESTAMPTZ DEFAULT NOW() -- 时区感知
);

-- 避免：不适当的类型
CREATE TABLE users_bad (
    id BIGINT,                          -- 不必要的大
    username TEXT,                      -- 无长度限制
    email TEXT,                         -- 无验证
    age INTEGER,                        -- 对年龄太大
    balance REAL,                       -- 对货币不精确
    is_active INTEGER,                  -- 不清楚（0/1？）
    created_at TIMESTAMP                -- 无时区
);

常见数据类型错误

错误 1：使用不带长度的 VARCHAR

sql

-- 意图不清
CREATE TABLE products (name VARCHAR);

-- 更好：指定长度或使用 TEXT
CREATE TABLE products (name VARCHAR(200));
CREATE TABLE products (name TEXT);

错误 2：对货币使用 REAL

sql

-- 错误：精度问题
CREATE TABLE orders (total REAL);

-- 正确：使用 NUMERIC
CREATE TABLE orders (total NUMERIC(10,2));

错误 3：不使用 SERIAL 进行自增

sql

-- 手动：更多工作
CREATE SEQUENCE user_id_seq;
CREATE TABLE users (
    id INTEGER DEFAULT nextval('user_id_seq')
);

-- 更好：使用 SERIAL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

错误 4：将日期存储为字符串

sql

-- 错误：字符串日期
CREATE TABLE events (event_date VARCHAR(10));
INSERT INTO events VALUES ('2024-01-01');

-- 正确：使用 DATE 类型
CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-01-01');

错误 5：不使用 TIMESTAMPTZ

sql

-- 错误：无时区
CREATE TABLE logs (created_at TIMESTAMP);

-- 正确：带时区
CREATE TABLE logs (created_at TIMESTAMPTZ);

数据类型大小比较

类型	存储	范围/精度
BOOLEAN	1 字节	true/false
SMALLINT	2 字节	-32,768 到 32,767
INTEGER	4 字节	-20 亿到 20 亿
BIGINT	8 字节	-900 万亿到 900 万亿
REAL	4 字节	6 位小数精度
DOUBLE PRECISION	8 字节	15 位小数精度
NUMERIC	可变	用户指定精度
CHAR(n)	n 字节	固定长度
VARCHAR(n)	可变	最多 n 个字符
TEXT	可变	无限长度
DATE	4 字节	仅日期
TIME	8 字节	仅时间
TIMESTAMP	8 字节	日期和时间
TIMESTAMPTZ	8 字节	日期、时间和时区
UUID	16 字节	唯一标识符
JSON	可变	基于文本的 JSON
JSONB	可变	二进制 JSON

总结

PostgreSQL 提供了丰富的数据类型集：

数值类型：INTEGER、BIGINT、NUMERIC、REAL、DOUBLE PRECISION
字符类型：CHAR、VARCHAR、TEXT
日期/时间类型：DATE、TIME、TIMESTAMP、TIMESTAMPTZ、INTERVAL
布尔类型：TRUE/FALSE 值
二进制类型：BYTEA 用于二进制数据
JSON 类型：JSON 和 JSONB 用于结构化数据
UUID：唯一标识符
数组：多值列
枚举：自定义枚举类型
几何类型：空间数据
网络类型：IP 和 MAC 地址
范围类型：值范围

关键要点：

为数据选择最小的适当类型
对精确的十进制计算使用 NUMERIC
始终对时间戳使用 TIMESTAMPTZ
优先使用 JSONB 而不是 JSON
使用 TEXT 而不是不带长度的 VARCHAR
使用适当的类型以获得更好的性能和数据完整性

理解并选择正确的数据类型是构建高效可靠的 PostgreSQL 数据库的基础。 CAST('123' AS INTEGER) AS int_value, CAST('2024-01-01' AS DATE) AS date_value, CAST(123.45 AS INTEGER) AS truncated, CAST('true' AS BOOLEAN) AS bool_value;


### :: 操作符（PostgreSQL 特有）

```sql
SELECT 
    '123'::INTEGER AS int_value,
    '2024-01-01'::DATE AS date_value,
    123.45::INTEGER AS truncated,
    'true'::BOOLEAN AS bool_value;

转换函数

sql

-- 字符串转数字
SELECT 
    to_number('1,234.56', '9,999.99') AS number_value,
    to_date('2024-01-01', 'YYYY-MM-DD') AS date_value,
    to_timestamp('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;

-- 数字转字符串
SELECT 
    to_char(1234.56, '9,999.99') AS formatted_number,
    to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_date;

选择正确的数据类型

最佳实践

使用适当的数值类型
- 对大多数整数使用 INTEGER
- 仅在需要时使用 BIGINT（节省空间）
- 对精确的十进制计算使用 NUMERIC（货币、测量）
- 避免对财务数据使用 REAL/DOUBLE PRECISION
字符串类型
- 对无限制的可变长度字符串使用 TEXT
- 仅在需要强制最大长度时使用 VARCHAR(n)
- 除非需要固定长度字符串，否则避免使用 CHAR(n)
日期/时间类型
- 始终对时间戳使用 TIMESTAMPTZ（处理时区）
- 对不带时间的日期使用 DATE
- 对持续时间使用 INTERVAL
布尔值 vs 整数
- 对真/假值使用 BOOLEAN（意图更清晰）
- 不要对布尔逻辑使用 INTEGER（0/1）
JSON 类型
- 优先使用 JSONB 而不是 JSON（更好的性能、索引）
- 对半结构化数据使用 JSONB
数组 vs 单独的表
- 对不需要查询的简单列表使用数组
- 对复杂关系使用单独的表

性能考虑

sql

-- 好：适当的类型
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- 自增
    username VARCHAR(50) NOT NULL,      -- 限制长度
    email VARCHAR(255) NOT NULL,        -- 标准邮箱长度
    age SMALLINT,                       -- 小数字
    balance NUMERIC(10,2),              -- 精确小数
    is_active BOOLEAN DEFAULT TRUE,     -- 清晰的布尔值
    created_at TIMESTAMPTZ DEFAULT NOW() -- 时区感知
);

-- 避免：不适当的类型
CREATE TABLE users_bad (
    id BIGINT,                          -- 不必要的大
    username TEXT,                      -- 无长度限制
    email TEXT,                         -- 无验证
    age INTEGER,                        -- 对年龄太大
    balance REAL,                       -- 对货币不精确
    is_active INTEGER,                  -- 不清楚（0/1？）
    created_at TIMESTAMP                -- 无时区
);

常见数据类型错误

错误 1：使用不带长度的 VARCHAR

sql

-- 意图不清
CREATE TABLE products (name VARCHAR);

-- 更好：指定长度或使用 TEXT
CREATE TABLE products (name VARCHAR(200));
CREATE TABLE products (name TEXT);

错误 2：对货币使用 REAL

sql

-- 错误：精度问题
CREATE TABLE orders (total REAL);

-- 正确：使用 NUMERIC
CREATE TABLE orders (total NUMERIC(10,2));

错误 3：不使用 SERIAL 进行自增

sql

-- 手动：更多工作
CREATE SEQUENCE user_id_seq;
CREATE TABLE users (
    id INTEGER DEFAULT nextval('user_id_seq')
);

-- 更好：使用 SERIAL
CREATE TABLE users (
    id SERIAL PRIMARY KEY
);

错误 4：将日期存储为字符串

sql

-- 错误：字符串日期
CREATE TABLE events (event_date VARCHAR(10));
INSERT INTO events VALUES ('2024-01-01');

-- 正确：使用 DATE 类型
CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-01-01');

错误 5：不使用 TIMESTAMPTZ

sql

-- 错误：无时区
CREATE TABLE logs (created_at TIMESTAMP);

-- 正确：带时区
CREATE TABLE logs (created_at TIMESTAMPTZ);

数据类型大小比较

类型	存储	范围/精度
BOOLEAN	1 字节	true/false
SMALLINT	2 字节	-32,768 到 32,767
INTEGER	4 字节	-20 亿到 20 亿
BIGINT	8 字节	-900 万亿到 900 万亿
REAL	4 字节	6 位小数精度
DOUBLE PRECISION	8 字节	15 位小数精度
NUMERIC	可变	用户指定精度
CHAR(n)	n 字节	固定长度
VARCHAR(n)	可变	最多 n 个字符
TEXT	可变	无限长度
DATE	4 字节	仅日期
TIME	8 字节	仅时间
TIMESTAMP	8 字节	日期和时间
TIMESTAMPTZ	8 字节	日期、时间和时区
UUID	16 字节	唯一标识符
JSON	可变	基于文本的 JSON
JSONB	可变	二进制 JSON

总结

PostgreSQL 提供了丰富的数据类型集：

数值类型：INTEGER、BIGINT、NUMERIC、REAL、DOUBLE PRECISION
字符类型：CHAR、VARCHAR、TEXT
日期/时间类型：DATE、TIME、TIMESTAMP、TIMESTAMPTZ、INTERVAL
布尔类型：TRUE/FALSE 值
二进制类型：BYTEA 用于二进制数据
JSON 类型：JSON 和 JSONB 用于结构化数据
UUID：唯一标识符
数组：多值列
枚举：自定义枚举类型
几何类型：空间数据
网络类型：IP 和 MAC 地址
范围类型：值范围

关键要点：

为数据选择最小的适当类型
对精确的十进制计算使用 NUMERIC
始终对时间戳使用 TIMESTAMPTZ
优先使用 JSONB 而不是 JSON
使用 TEXT 而不是不带长度的 VARCHAR
使用适当的类型以获得更好的性能和数据完整性

理解并选择正确的数据类型是构建高效可靠的 PostgreSQL 数据库的基础。

PostgreSQL 数据类型 ​

简介 ​

数据类型分类 ​

数值类型 ​

整数类型 ​

SMALLINT 示例 ​

INTEGER 示例 ​

BIGINT 示例 ​

序列类型（自增） ​

浮点类型 ​

精确数值类型 ​

货币类型 ​

字符类型 ​

CHAR 示例 ​

VARCHAR 示例 ​

TEXT 示例 ​

布尔类型 ​

日期/时间类型 ​

DATE 示例 ​

TIME 示例 ​

TIMESTAMP 示例 ​

TIMESTAMPTZ 示例 ​

INTERVAL 示例 ​

二进制数据类型 ​

JSON 类型 ​

JSON 示例 ​

JSONB 示例 ​

UUID 类型 ​

数组类型 ​

枚举类型 ​

几何类型 ​

网络地址类型 ​

范围类型 ​

复合类型 ​

XML 类型 ​

位串类型 ​

数据类型转换 ​

CAST 函数 ​

:: 操作符（PostgreSQL 特有） ​

转换函数 ​

选择正确的数据类型 ​

最佳实践 ​

性能考虑 ​

常见数据类型错误 ​

错误 1：使用不带长度的 VARCHAR ​

错误 2：对货币使用 REAL ​

错误 3：不使用 SERIAL 进行自增 ​

错误 4：将日期存储为字符串 ​

错误 5：不使用 TIMESTAMPTZ ​

数据类型大小比较 ​

总结 ​

转换函数 ​

选择正确的数据类型 ​

最佳实践 ​

性能考虑 ​

常见数据类型错误 ​

错误 1：使用不带长度的 VARCHAR ​

错误 2：对货币使用 REAL ​

错误 3：不使用 SERIAL 进行自增 ​

错误 4：将日期存储为字符串 ​

错误 5：不使用 TIMESTAMPTZ ​

数据类型大小比较 ​

总结 ​

PostgreSQL 数据类型

简介

数据类型分类

数值类型

整数类型

SMALLINT 示例

INTEGER 示例

BIGINT 示例

序列类型（自增）

浮点类型

精确数值类型

货币类型

字符类型

CHAR 示例

VARCHAR 示例

TEXT 示例

布尔类型

日期/时间类型

DATE 示例

TIME 示例

TIMESTAMP 示例

TIMESTAMPTZ 示例

INTERVAL 示例

二进制数据类型

JSON 类型

JSON 示例

JSONB 示例

UUID 类型

数组类型

枚举类型

几何类型

网络地址类型

范围类型

复合类型

XML 类型

位串类型

数据类型转换

CAST 函数

:: 操作符（PostgreSQL 特有）

转换函数

选择正确的数据类型

最佳实践

性能考虑

常见数据类型错误

错误 1：使用不带长度的 VARCHAR

错误 2：对货币使用 REAL

错误 3：不使用 SERIAL 进行自增

错误 4：将日期存储为字符串

错误 5：不使用 TIMESTAMPTZ

数据类型大小比较

总结

转换函数

选择正确的数据类型

最佳实践

性能考虑

常见数据类型错误

错误 1：使用不带长度的 VARCHAR

错误 2：对货币使用 REAL

错误 3：不使用 SERIAL 进行自增

错误 4：将日期存储为字符串

错误 5：不使用 TIMESTAMPTZ

数据类型大小比较

总结