PostgreSQL 表操作 #
表概述 #
表是数据库中存储数据的基本单位，由行（记录）和列（字段）组成。
text
┌─────────────────────────────────────────────────────────────┐
│ 表的结构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ users 表 │
│ ┌──────┬──────────┬─────────────────┬───────────────────┐│
│ │ id │ name │ email │ created_at ││
│ ├──────┼──────────┼─────────────────┼───────────────────┤│
│ │ 1 │ Alice │ alice@test.com │ 2026-03-29 10:00 ││
│ │ 2 │ Bob │ bob@test.com │ 2026-03-29 11:00 ││
│ │ 3 │ Carol │ carol@test.com │ 2026-03-29 12:00 ││
│ └──────┴──────────┴─────────────────┴───────────────────┘│
│ │
│ 列（字段） 行（记录） │
│ │
└─────────────────────────────────────────────────────────────┘
创建表 #
CREATE TABLE 基本语法 #
sql
CREATE TABLE table_name (
column1 data_type [column_constraints],
column2 data_type [column_constraints],
...,
[table_constraints]
);
创建简单表 #
sql
-- 创建用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INTEGER,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 查看表结构
\d users
-- 输出：
-- Table "public.users"
-- Column | Type | Collation | Nullable | Default
-- -------------+-----------------------------+-----------+----------+-----------------------------------
-- id | integer | | not null | nextval('users_id_seq'::regclass)
-- username | character varying(50) | | not null |
-- email | character varying(100) | | |
-- age | integer | | |
-- is_active | boolean | | | true
-- created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
-- Indexes:
-- "users_pkey" PRIMARY KEY, btree (id)
-- "users_email_key" UNIQUE CONSTRAINT, btree (email)
使用 IF NOT EXISTS #
sql
-- 如果表不存在则创建
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
创建带注释的表 #
sql
-- 创建表
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
description TEXT
);
-- 添加表注释
COMMENT ON TABLE products IS '商品信息表';
-- 添加列注释
COMMENT ON COLUMN products.id IS '商品ID，主键';
COMMENT ON COLUMN products.name IS '商品名称';
COMMENT ON COLUMN products.price IS '商品价格，单位：元';
COMMENT ON COLUMN products.description IS '商品描述';
约束类型 #
主键约束（PRIMARY KEY） #
sql
-- 单列主键
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
-- 等价于
CREATE TABLE users (
id SERIAL,
name VARCHAR(100),
PRIMARY KEY (id)
);
-- 复合主键
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
外键约束（FOREIGN KEY） #
sql
-- 创建主表
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- 创建从表，带外键
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INTEGER REFERENCES categories(id),
price DECIMAL(10, 2)
);
-- 完整的外键语法
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INTEGER,
price DECIMAL(10, 2),
CONSTRAINT fk_products_category
FOREIGN KEY (category_id)
REFERENCES categories(id)
ON DELETE CASCADE -- 删除主表记录时级联删除
ON UPDATE CASCADE -- 更新主表记录时级联更新
);
-- 外键引用行为
-- ON DELETE/UPDATE:
-- CASCADE - 级联操作
-- SET NULL - 设置为 NULL
-- SET DEFAULT - 设置为默认值
-- RESTRICT - 限制，不允许操作（默认）
-- NO ACTION - 同 RESTRICT
唯一约束（UNIQUE） #
sql
-- 列级唯一约束
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
-- 表级唯一约束
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
CONSTRAINT uq_users_username UNIQUE (username),
CONSTRAINT uq_users_email UNIQUE (email)
);
-- 复合唯一约束
CREATE TABLE user_roles (
user_id INTEGER,
role_id INTEGER,
UNIQUE (user_id, role_id)
);
非空约束（NOT NULL） #
sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20) -- 可以为 NULL
);
检查约束（CHECK） #
sql
-- 列级检查约束
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) CHECK (price > 0),
stock INTEGER CHECK (stock >= 0)
);
-- 表级检查约束
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_orders_dates CHECK (end_date > start_date)
);
-- 多列检查约束
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
salary DECIMAL(10, 2),
bonus DECIMAL(10, 2),
CONSTRAINT chk_employee_compensation CHECK (salary + bonus > 0)
);
默认值约束（DEFAULT） #
sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 使用函数作为默认值
CREATE TABLE sessions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
token VARCHAR(255) DEFAULT md5(random()::TEXT),
expires_at TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '24 hours')
);
排除约束（EXCLUDE） #
sql
-- 排除约束确保某些列的组合不满足特定条件
-- 需要安装 btree_gist 扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- 确保同一会议室的时间不重叠
CREATE TABLE room_bookings (
id SERIAL PRIMARY KEY,
room_id INTEGER NOT NULL,
during TSRANGE NOT NULL,
EXCLUDE USING GIST (
room_id WITH =,
during WITH &&
)
);
-- 插入测试
INSERT INTO room_bookings (room_id, during) VALUES
(1, '[2026-03-29 10:00, 2026-03-29 12:00)');
-- 这条会失败，因为时间重叠
-- INSERT INTO room_bookings (room_id, during) VALUES
-- (1, '[2026-03-29 11:00, 2026-03-29 13:00)');
修改表 #
ALTER TABLE 概述 #
sql
-- ALTER TABLE 可以：
-- 1. 添加/删除列
-- 2. 修改列类型
-- 3. 添加/删除约束
-- 4. 重命名表/列
-- 5. 修改列默认值
添加列 #
sql
-- 添加单列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 添加带约束的列
ALTER TABLE users ADD COLUMN age INTEGER CHECK (age >= 0);
-- 添加带默认值的列
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- 添加多列
ALTER TABLE users
ADD COLUMN address TEXT,
ADD COLUMN city VARCHAR(100);
删除列 #
sql
-- 删除列
ALTER TABLE users DROP COLUMN phone;
-- 如果列存在则删除
ALTER TABLE users DROP COLUMN IF EXISTS phone;
-- 强制删除（删除依赖对象）
ALTER TABLE users DROP COLUMN phone CASCADE;
修改列 #
sql
-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(50);
-- 修改列类型并转换数据
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(50)
USING phone::VARCHAR(50);
-- 修改列名
ALTER TABLE users RENAME COLUMN phone TO phone_number;
-- 设置默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';
-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- 设置非空
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
-- 删除非空约束
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;
添加约束 #
sql
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 添加唯一约束
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
-- 添加检查约束
ALTER TABLE products
ADD CONSTRAINT chk_products_price CHECK (price > 0);
删除约束 #
sql
-- 删除约束（需要知道约束名）
ALTER TABLE users DROP CONSTRAINT uq_users_email;
-- 查看约束名
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;
-- 删除主键
ALTER TABLE users DROP CONSTRAINT users_pkey;
-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;
重命名表 #
sql
-- 重命名表
ALTER TABLE users RENAME TO app_users;
-- 重命名表和列
ALTER TABLE users RENAME TO app_users;
ALTER TABLE app_users RENAME COLUMN name TO username;
删除表 #
DROP TABLE #
sql
-- 删除表
DROP TABLE users;
-- 如果存在则删除
DROP TABLE IF EXISTS users;
-- 强制删除（删除依赖对象）
DROP TABLE users CASCADE;
-- 删除多个表
DROP TABLE users, orders, products;
TRUNCATE TABLE #
sql
-- 清空表数据（保留表结构）
TRUNCATE TABLE users;
-- 清空并重置自增序列
TRUNCATE TABLE users RESTART IDENTITY;
-- 清空多个表
TRUNCATE TABLE users, orders;
-- 级联清空（清空有外键引用的表）
TRUNCATE TABLE users CASCADE;
-- TRUNCATE vs DELETE
-- TRUNCATE: 更快，不触发触发器，重置序列
-- DELETE: 较慢，触发触发器，不重置序列
临时表 #
创建临时表 #
sql
-- 创建临时表（会话结束后自动删除）
CREATE TEMP TABLE temp_users AS
SELECT id, name FROM users WHERE status = 'active';
-- 创建临时表并指定结构
CREATE TEMP TABLE temp_orders (
id INTEGER,
total DECIMAL(10, 2)
);
-- 临时表只在当前会话可见
-- 会话结束后自动删除
临时表特性 #
sql
-- 临时表在会话结束时自动删除
-- 临时表默认在临时 schema 中
-- 可以创建同名临时表隐藏永久表
CREATE TABLE my_table (id INT); -- 永久表
CREATE TEMP TABLE my_table (id INT); -- 临时表（隐藏永久表）
-- 查看所有临时表
SELECT * FROM pg_tables WHERE schemaname = 'pg_temp_%';
表继承 #
创建继承表 #
sql
-- 父表
CREATE TABLE persons (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
birth_date DATE
);
-- 子表继承父表
CREATE TABLE employees (
employee_id VARCHAR(20),
hire_date DATE,
salary DECIMAL(10, 2)
) INHERITS (persons);
-- 子表包含父表的所有列
\d employees
-- Column | Type | Collation | Nullable | Default
-- ------------+-----------------------+-----------+----------+-----------------------------------
-- id | integer | | not null | nextval('persons_id_seq'::regclass)
-- name | character varying(100)| | |
-- birth_date | date | | |
-- employee_id| character varying(20) | | |
-- hire_date | date | | |
-- salary | numeric(10,2) | | |
继承表查询 #
sql
-- 查询父表（只返回父表数据）
SELECT * FROM ONLY persons;
-- 查询父表（返回父表和所有子表数据）
SELECT * FROM persons;
-- 查询子表
SELECT * FROM employees;
分区表 #
范围分区 #
sql
-- 创建分区表（父表）
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
customer_id INTEGER,
amount DECIMAL(10, 2)
) PARTITION BY RANGE (order_date);
-- 创建分区
CREATE TABLE orders_2026_01 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026_03 PARTITION OF orders
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- 创建默认分区
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
-- 插入数据会自动路由到对应分区
INSERT INTO orders (order_date, customer_id, amount) VALUES
('2026-01-15', 1, 100.00),
('2026-02-20', 2, 200.00),
('2026-03-10', 3, 300.00);
列表分区 #
sql
-- 创建列表分区表
CREATE TABLE customers (
id SERIAL,
name VARCHAR(100),
region VARCHAR(20)
) PARTITION BY LIST (region);
-- 创建分区
CREATE TABLE customers_east PARTITION OF customers
FOR VALUES IN ('Beijing', 'Shanghai', 'Guangzhou');
CREATE TABLE customers_west PARTITION OF customers
FOR VALUES IN ('Chengdu', 'Chongqing', 'Xian');
CREATE TABLE customers_default PARTITION OF customers DEFAULT;
哈希分区 #
sql
-- 创建哈希分区表
CREATE TABLE logs (
id SERIAL,
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH (id);
-- 创建分区
CREATE TABLE logs_0 PARTITION OF logs
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_1 PARTITION OF logs
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_2 PARTITION OF logs
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_3 PARTITION OF logs
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
查看表信息 #
使用 psql 命令 #
sql
-- 查看表结构
\d users
-- 查看详细表结构
\d+ users
-- 列出所有表
\dt
-- 列出所有表（包括系统表）
\dt+
-- 查看表索引
\di
-- 查看表约束
\dc
使用 SQL 查询 #
sql
-- 查看表结构
SELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
-- 查看表约束
SELECT
tc.constraint_name,
tc.constraint_type,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'users';
-- 查看表大小
SELECT
pg_size_pretty(pg_total_relation_size('users')) AS total_size,
pg_size_pretty(pg_relation_size('users')) AS table_size,
pg_size_pretty(pg_total_relation_size('users') - pg_relation_size('users')) AS index_size;
-- 查看表统计信息
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';
最佳实践 #
表设计原则 #
text
┌─────────────────────────────────────────────────────────────┐
│ 表设计原则 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 规范化设计 │
│ 避免数据冗余，合理使用外键 │
│ │
│ 2. 选择合适的数据类型 │
│ 不要过度使用 TEXT，选择合适的数值类型 │
│ │
│ 3. 添加适当的约束 │
│ NOT NULL、CHECK、外键保证数据完整性 │
│ │
│ 4. 合理使用索引 │
│ 主键自动创建索引，外键和常用查询列考虑索引 │
│ │
│ 5. 表和列命名规范 │
│ 小写、下划线分隔、见名知意 │
│ │
│ 6. 添加注释 │
│ 为表和重要列添加注释 │
│ │
└─────────────────────────────────────────────────────────────┘
命名规范 #
sql
-- 表名：小写，复数，下划线分隔
users, order_items, product_categories
-- 列名：小写，下划线分隔
user_name, created_at, is_active
-- 主键：id 或 表名_id
id, user_id, order_id
-- 外键：关联表名_id
category_id, user_id
-- 布尔列：is_xxx, has_xxx
is_active, is_deleted, has_permission
-- 时间列：xxx_at
created_at, updated_at, deleted_at
学习路径 #
text
基础阶段
├── 数据库操作
├── 表操作（本文）
├── 数据CRUD
└── 基础查询
下一步 #
掌握了表操作后，接下来学习 数据插入，开始向表中添加数据！
最后更新：2026-03-29