PostgreSQL 数据插入 #
INSERT 概述 #
INSERT 语句用于向表中插入新数据。
text
┌─────────────────────────────────────────────────────────────┐
│ INSERT 语句结构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ INSERT INTO table_name (column1, column2, ...) │
│ VALUES (value1, value2, ...) │
│ [ RETURNING * | column1, column2, ... ]; │
│ │
│ 组成部分： │
│ ├── INSERT INTO table_name 指定目标表 │
│ ├── (columns) 指定列（可选） │
│ ├── VALUES (values) 指定值 │
│ └── RETURNING 返回插入的数据（可选） │
│ │
└─────────────────────────────────────────────────────────────┘
基本插入 #
插入单行数据 #
sql
-- 创建示例表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
age INTEGER,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入完整行（指定所有列）
INSERT INTO users (id, name, email, age, status, created_at)
VALUES (DEFAULT, 'Alice', 'alice@example.com', 25, 'active', DEFAULT);
-- 插入部分列（未指定的列使用默认值或 NULL）
INSERT INTO users (name, email, age)
VALUES ('Bob', 'bob@example.com', 30);
-- 只插入必填列
INSERT INTO users (name)
VALUES ('Charlie');
-- 使用 DEFAULT 关键字
INSERT INTO users (name, email, status)
VALUES ('David', 'david@example.com', DEFAULT);
省略列名 #
sql
-- 省略列名时，必须按表定义顺序提供所有列的值
INSERT INTO users
VALUES (DEFAULT, 'Eve', 'eve@example.com', 28, 'active', DEFAULT);
-- 不推荐：列顺序变化会导致错误
批量插入 #
多行 VALUES #
sql
-- 一次插入多行
INSERT INTO users (name, email, age) VALUES
('Frank', 'frank@example.com', 35),
('Grace', 'grace@example.com', 28),
('Henry', 'henry@example.com', 42);
-- 批量插入性能更好
-- 比多次单行插入快得多
从查询插入 #
sql
-- 创建备份表
CREATE TABLE users_backup (
id INTEGER,
name VARCHAR(100),
email VARCHAR(100),
backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 从查询结果插入
INSERT INTO users_backup (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';
-- 从另一个表复制数据
CREATE TABLE users_archive (LIKE users);
INSERT INTO users_archive
SELECT * FROM users WHERE created_at < '2025-01-01';
RETURNING 子句 #
PostgreSQL 特有的 RETURNING 子句可以返回插入的数据：
sql
-- 返回所有列
INSERT INTO users (name, email, age)
VALUES ('Ivan', 'ivan@example.com', 33)
RETURNING *;
-- 输出：
-- id | name | email | age | status | created_at
-- ----+-------+--------------------+-----+--------+----------------------------
-- 8 | Ivan | ivan@example.com | 33 | active | 2026-03-29 10:00:00.000000
-- 返回特定列
INSERT INTO users (name, email)
VALUES ('Julia', 'julia@example.com')
RETURNING id, name, created_at;
-- 返回生成的 ID
INSERT INTO users (name, email)
VALUES ('Kevin', 'kevin@example.com')
RETURNING id;
-- 在应用中使用
-- INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;
插入冲突处理（UPSERT） #
ON CONFLICT 语法 #
sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [conflict_target] conflict_action;
-- conflict_target:
-- (column_name) 指定冲突列
-- ON CONSTRAINT constraint_name 指定约束名
-- WHERE predicate 条件
-- conflict_action:
-- DO NOTHING 忽略冲突
-- DO UPDATE SET ... 更新数据
DO NOTHING #
sql
-- 如果 email 冲突，忽略插入
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 26)
ON CONFLICT (email) DO NOTHING;
-- 使用约束名
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING;
DO UPDATE（UPSERT） #
sql
-- 如果 email 冲突，更新数据
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 26)
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
age = EXCLUDED.age,
status = 'updated';
-- EXCLUDED 表包含尝试插入的数据
-- 使用条件更新
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 26)
ON CONFLICT (email)
DO UPDATE SET
age = EXCLUDED.age
WHERE users.age < EXCLUDED.age; -- 只在新年龄更大时更新
-- 返回结果
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 26)
ON CONFLICT (email)
DO UPDATE SET age = EXCLUDED.age
RETURNING id, name, (xmax = 0) AS inserted;
-- inserted = true 表示新插入
-- inserted = false 表示更新
COPY 命令 #
从文件导入 #
sql
-- 创建表
CREATE TABLE products (
id INTEGER,
name VARCHAR(100),
price DECIMAL(10, 2),
category VARCHAR(50)
);
-- 从 CSV 文件导入
COPY products FROM '/path/to/products.csv'
DELIMITER ','
CSV HEADER;
-- 从文本文件导入
COPY products FROM '/path/to/products.txt'
DELIMITER E'\t';
-- 指定列
COPY products (name, price, category)
FROM '/path/to/products.csv'
DELIMITER ',' CSV HEADER;
-- 指定编码
COPY products FROM '/path/to/products.csv'
DELIMITER ','
CSV HEADER
ENCODING 'UTF8';
导出数据 #
sql
-- 导出为 CSV
COPY users TO '/path/to/users.csv'
DELIMITER ','
CSV HEADER;
-- 导出查询结果
COPY (
SELECT id, name, email
FROM users
WHERE status = 'active'
) TO '/path/to/active_users.csv'
DELIMITER ','
CSV HEADER;
-- 导出为文本
COPY users TO '/path/to/users.txt'
DELIMITER E'\t';
-- 导出二进制格式
COPY users TO '/path/to/users.bin' WITH BINARY;
COPY 参数说明 #
text
┌─────────────────┬─────────────────────────────────────────────┐
│ 参数 │ 说明 │
├─────────────────┼─────────────────────────────────────────────┤
│ DELIMITER │ 分隔符，默认制表符 │
│ CSV │ CSV 格式 │
│ HEADER │ 第一行是列名 │
│ QUOTE │ 引号字符，默认 " │
│ ESCAPE │ 转义字符，默认 " │
│ NULL │ NULL 值表示，默认 \N │
│ ENCODING │ 文件编码 │
│ BINARY │ 二进制格式 │
└─────────────────┴─────────────────────────────────────────────┘
客户端 COPY #
sql
-- \copy 是 psql 客户端命令，不需要超级用户权限
-- 从文件导入
\copy products FROM '/path/to/products.csv' CSV HEADER
-- 导出到文件
\copy users TO '/path/to/users.csv' CSV HEADER
-- 导出查询结果
\copy (SELECT * FROM users WHERE status = 'active') TO '/path/to/active_users.csv' CSV HEADER
插入默认值 #
sql
-- 使用 DEFAULT VALUES 插入所有默认值
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
message TEXT DEFAULT 'No message',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
level VARCHAR(10) DEFAULT 'INFO'
);
INSERT INTO logs DEFAULT VALUES;
-- 混合使用
INSERT INTO logs (message) VALUES ('Error occurred');
插入特殊值 #
sql
-- 插入 NULL
INSERT INTO users (name, email, age)
VALUES ('Test', NULL, NULL);
-- 插入表达式结果
INSERT INTO users (name, email, created_at)
VALUES (
'Generated',
'gen_' || md5(random()::TEXT) || '@example.com',
CURRENT_TIMESTAMP + INTERVAL '1 day'
);
-- 插入数组
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
tags VARCHAR(50)[]
);
INSERT INTO projects (name, tags) VALUES
('Project A', ARRAY['web', 'frontend']),
('Project B', '{"backend", "api"}');
-- 插入 JSON
CREATE TABLE settings (
id SERIAL PRIMARY KEY,
config JSONB
);
INSERT INTO settings (config) VALUES
('{"theme": "dark", "language": "zh-CN"}'),
('{"theme": "light", "language": "en-US"}');
性能优化 #
批量插入优化 #
sql
-- 不推荐：循环单行插入
-- INSERT INTO users (name) VALUES ('User1');
-- INSERT INTO users (name) VALUES ('User2');
-- INSERT INTO users (name) VALUES ('User3');
-- 推荐：批量插入
INSERT INTO users (name) VALUES
('User1'),
('User2'),
('User3');
-- 大批量数据使用 COPY
COPY users (name, email) FROM '/path/to/large_data.csv' CSV;
禁用索引和约束 #
sql
-- 大批量插入前禁用索引
DROP INDEX idx_users_email;
-- 插入数据
COPY users FROM '/path/to/users.csv' CSV;
-- 重建索引
CREATE INDEX idx_users_email ON users(email);
-- 或使用事务
BEGIN;
ALTER TABLE users DROP CONSTRAINT users_email_key;
COPY users FROM '/path/to/users.csv' CSV;
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
COMMIT;
使用 UNLOGGED 表 #
sql
-- 创建 UNLOGGED 表（不写 WAL 日志，更快但崩溃会丢失数据）
CREATE UNLOGGED TABLE temp_data (
id SERIAL PRIMARY KEY,
data TEXT
);
-- 批量插入
INSERT INTO temp_data (data)
SELECT 'data_' || generate_series(1, 100000);
-- 处理完后转为普通表
ALTER TABLE temp_data SET LOGGED;
常见错误处理 #
违反约束 #
sql
-- 主键冲突
-- ERROR: duplicate key value violates unique constraint "users_pkey"
INSERT INTO users (id, name) VALUES (1, 'Duplicate');
-- 解决：使用 ON CONFLICT 或跳过重复值
-- 外键约束失败
-- ERROR: insert or update on table "orders" violates foreign key constraint
INSERT INTO orders (user_id, amount) VALUES (999, 100);
-- 解决：确保外键值存在
-- 非空约束
-- ERROR: null value in column "name" violates not-null constraint
INSERT INTO users (name, email) VALUES (NULL, 'test@example.com');
-- 解决：提供值或修改约束
类型转换错误 #
sql
-- 类型不匹配
-- ERROR: invalid input syntax for type integer
INSERT INTO users (name, age) VALUES ('Test', 'not_a_number');
-- 解决：确保类型正确或显式转换
INSERT INTO users (name, age) VALUES ('Test', '25'::INTEGER);
学习路径 #
text
基础阶段
├── 数据库操作
├── 表操作
├── 数据插入（本文）
└── 数据更新
下一步 #
掌握了数据插入后，接下来学习 数据更新，了解如何修改已有数据！
最后更新：2026-03-29