PostgreSQL INSERT INTO 语句完全指南

涵盖 基础语法、多行插入、返回数据、冲突处理、JSON/数组、批量导入、性能优化 等全部场景。

---

一、基本语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

---

二、核心用法（5 种写法）

写法	示例	说明
1. 标准插入	INSERT INTO users (name, email) VALUES ('Alice', 'a@example.com');	推荐
2. 省略列名	INSERT INTO users VALUES (1, 'Bob', 'b@ex.com');	必须按表顺序
3. 多行插入	INSERT INTO users (name) VALUES ('C'), ('D'), ('E');	性能更高
4. 默认值	INSERT INTO users (name) VALUES ('F');	其他列用 DEFAULT
5. 返回插入数据	INSERT INTO users (name) VALUES ('G') RETURNING id, name;	非常实用

---

三、完整推荐建表示例

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username CITEXT NOT NULL UNIQUE,
    email CITEXT,
    age SMALLINT CHECK (age >= 0),
    tags TEXT[] DEFAULT '{}',
    profile JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now()
);

---

四、INSERT 实战示例

1. 单行插入

INSERT INTO users (username, email, age, tags, profile)
VALUES (
    'alice',
    'alice@example.com',
    25,
    '{"dev", "admin"}',
    '{"level": 5, "theme": "dark"}'
);

---

2. 多行插入（推荐，性能好）

INSERT INTO users (username, email, age)
VALUES
    ('bob', 'bob@ex.com', 30),
    ('carol', 'carol@ex.com', 28),
    ('dave', 'dave@ex.com', 35);

---

3. 使用 DEFAULT

-- 只插入 username，其他用默认值
INSERT INTO users (username) VALUES ('eve');

---

4. 插入 JSON 和数组

INSERT INTO users (username, profile, tags)
VALUES (
    'frank',
    '{"settings": {"notifications": true}}'::jsonb,
    ARRAY['user', 'beta']
);

---

5. 返回插入的 ID（关键！）

INSERT INTO users (username) 
VALUES ('grace') 
RETURNING id;
-- 输出: 7

-- 返回多列
INSERT INTO users (username, email) 
VALUES ('hank', 'hank@ex.com') 
RETURNING id, username, created_at;

---

五、冲突处理（UPSERT）—— PostgreSQL 杀手级特性

场景：插入时如果主键/唯一键冲突 → 更新

-- 语法
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'new@email.com')
ON CONFLICT (id) DO UPDATE 
SET email = EXCLUDED.email;

常见冲突目标

目标	语法
主键	ON CONFLICT (id)
唯一索引	ON CONFLICT (username)
多个列	ON CONFLICT (col1, col2)
任意冲突	ON CONFLICT DO NOTHING

示例：用户名唯一，冲突时更新 email

INSERT INTO users (username, email, age)
VALUES ('alice', 'alice.new@ex.com', 26)
ON CONFLICT (username) DO UPDATE 
SET email = EXCLUDED.email, 
    age = EXCLUDED.age;

示例：冲突时什么都不做

INSERT INTO users (username) 
VALUES ('alice') 
ON CONFLICT (username) DO NOTHING;

---

六、从其他表插入（INSERT … SELECT）

-- 从旧表迁移到新表
INSERT INTO users_new (username, email)
SELECT username, email FROM users_old;

-- 带条件 + 计算
INSERT INTO user_stats (user_id, login_count)
SELECT id, 1 FROM users WHERE is_active = true;

---

七、批量插入性能优化

技巧	说明
多行 VALUES	100 行一组，减少网络往返
COPY 命令	百万级数据首选
临时表 + INSERT SELECT	复杂逻辑
禁用触发器（慎用）	ALTER TABLE users DISABLE TRIGGER ALL;

COPY 导入（最快）

-- 从 CSV 文件
COPY users (username, email) FROM '/tmp/users.csv' DELIMITER ',' CSV HEADER;

-- 从程序输入
COPY users (username) FROM STDIN;
alice
bob
\.

---

八、插入并获取多条返回数据

INSERT INTO users (username)
VALUES ('user1'), ('user2'), ('user3')
RETURNING id, username;

输出：

 id | username
----+----------
 10 | user1
 11 | user2
 12 | user3

---

九、常见错误与解决

错误	原因	解决
column "xxx" does not exist	列名拼错	检查 \d users
null value in column "username" violates not-null	必填列未提供	补上值或 DEFAULT
duplicate key value violates unique constraint	唯一键冲突	用 ON CONFLICT
invalid input syntax for type json	JSON 格式错	用 '{}'::jsonb
array value must start with "{"	数组语法错	用 ARRAY[...] 或 '{"a"}'

---

十、最佳实践 Checklist

项目	建议
列名明确	永远写 (col1, col2)，不要省略
多行插入	每批 100~1000 行
RETURNING	插入后立即获取 ID
ON CONFLICT	避免重复插入错误
COPY	批量导入用
事务	大批量用 BEGIN; ... COMMIT;

---

十一、一键插入 1000 条测试数据

INSERT INTO users (username, email, age, is_active)
SELECT 
    'user_' || n,
    'user_' || n || '@test.com',
    (random() * 80 + 10)::int,
    true
FROM generate_series(1, 1000) AS n;

---

十二、Python 中使用 INSERT

import psycopg2

conn = psycopg2.connect(dbname="mydb", user="appuser")
cur = conn.cursor()

# 1. 插入并返回 ID
cur.execute(
    "INSERT INTO users (username) VALUES (%s) RETURNING id",
    ('python_user',)
)
user_id = cur.fetchone()[0]

# 2. 批量插入
data = [('u1', 'u1@ex.com'), ('u2', 'u2@ex.com')]
cur.executemany("INSERT INTO users (username, email) VALUES (%s, %s)", data)

conn.commit()

---

十三、快速参考表

命令	说明
INSERT ... VALUES (...);	单行
INSERT ... VALUES (...), (...);	多行
INSERT ... RETURNING id;	返回 ID
INSERT ... ON CONFLICT DO UPDATE	UPSERT
INSERT ... ON CONFLICT DO NOTHING	忽略冲突
COPY table FROM '...'	批量导入

---

小结：INSERT 黄金 3 原则

1. 永远写列名：(username, email)
2. 多行一起插：VALUES (...), (...), (...)
3. 永远用 RETURNING 获取 ID

---

现在就动手：

1. 创建 products 表（id, name, price, tags）
2. 用多行 INSERT 插入 5 条数据
3. 用 RETURNING 获取 ID
4. 再插入一条重复 name，用 ON CONFLICT DO NOTHING

---

需要我生成：

• 10000 条测试数据脚本？回复 测试数据
• CSV 批量导入模板？回复 CSV 导入
• UPSERT 高级用法（更新 JSON 字段）？回复 UPSERT JSON
• Node.js 批量插入示例？回复 Node.js

随时告诉我！