PostgreSQL COPY

一、COPY 是什么？

COPY 是 PostgreSQL 最快的数据导入 / 导出方式，直接在 数据库服务端 读写文件。

文件 <-> PostgreSQL 后端进程 <-> 表

性能远超 INSERT / ORM / 批量执行

二、COPY 基本语法

1️⃣ 从文件导入表

COPY table_name
FROM '/absolute/path/file.csv'
WITH (FORMAT csv);

2️⃣ 从表导出文件

COPY table_name
TO '/absolute/path/file.csv'
WITH (FORMAT csv, HEADER true);

3️⃣ 指定列（最常用）

COPY articles (datetime, code, source, title, url)
FROM '/tmp/articles.csv'
WITH (FORMAT csv, HEADER true);

避免列顺序问题
可跳过自增 id

三、COPY 的常用参数（非常重要）

CSV 相关

WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    QUOTE '"',
    ESCAPE '"',
    NULL '',
    ENCODING 'UTF8'
);

参数	说明
FORMAT	格式
HEADER	首行是列名
DELIMITER	字段分隔符
QUOTE	字段包裹符（默认为 “）
ESCAPE	转义符（默认为 “）
NULL	空值表示
ENCODING	文件编码

时间 & 空值 🌟

SET datestyle = 'ISO, YMD';

2025-01-01 10:00:00,,source,title,url

四、COPY vs \copy（99% 的坑在这）

对比项	COPY	\copy
执行位置	数据库服务器	客户端（psql）
文件位置	服务器磁盘	本地磁盘
权限	需要 superuser / pg_read_server_files	普通用户即可
常用场景	生产服务器	本地导入

客户端导入（推荐）

\copy articles (datetime, code, source, title, url)
FROM 'articles.csv'
WITH (FORMAT csv, HEADER true);

👉 优先用 \copy，少踩权限坑

五、IDENTITY / 自增字段与 COPY（你刚踩的坑）

❌ 错误场景

id INTEGER GENERATED ALWAYS AS IDENTITY

CSV 包含 id -> 报错

✅ 正确方式 1（推荐）

COPY articles (datetime, code, source, title, url)
FROM '/path/articles.csv'
WITH (FORMAT csv, HEADER true);

✅ 正确方式 2（保留 id）

COPY articles (id, datetime, code, source, title, url)
FROM '/path/articles.csv'
WITH (FORMAT csv, HEADER true)
OVERRIDING SYSTEM VALUE;

OVERRIDING SYSTEM VALUE 可能不可用

导入后修复序列：

SELECT setval(
    pg_get_serial_sequence('articles', 'id'),
    (SELECT MAX(id) FROM articles)
);

六、COPY 性能优化（生产级）

🚀 1️⃣ 关闭同步写（导入期间）

SET synchronous_commit = off;

🚀 2️⃣ 临时删除索引（大表）

DROP INDEX idx_articles_title;
COPY ...
CREATE INDEX idx_articles_title ON articles (...);

🚀 3️⃣ 使用 UNLOGGED 表（中间表）

CREATE UNLOGGED TABLE articles_tmp (...);

🚀 4️⃣ 批量导入百万行建议参数

maintenance_work_mem = 1GB
work_mem = 64MB

七、COPY + 事务行为（重要）

BEGIN;
COPY articles FROM '/data/a.csv' CSV;
COMMIT;
-- 出错 -> 回滚
-- ROLLBACK;

COPY 是事务性的
出错 -> 整个 COPY 回滚

八、COPY 常见错误 & 解决方案

❌ 路径不存在

could not open file

文件必须在数据库服务器

❌ 权限不足

must be superuser

用 \copy 或授予：

使用 COPY 会有权限问题

GRANT pg_read_server_files TO user;
GRANT pg_write_server_files TO user;

❌ 编码错误

invalid byte sequence

转码：

iconv -f gbk -t utf8 a.csv > b.csv

❌ CSV 列错位

明确列名
检查 delimiter / quote

九、COPY 高级用法

1️⃣ COPY 查询结果

COPY (
    SELECT * FROM articles WHERE datetime >= '2025-01-01'
) TO '/data/export.csv' CSV HEADER;

2️⃣ COPY + ON CONFLICT（间接）

CREATE TEMP TABLE tmp_articles (LIKE articles);

COPY tmp_articles FROM '/data/a.csv' CSV HEADER;

INSERT INTO articles
SELECT * FROM tmp_articles
ON CONFLICT (code, url) DO NOTHING;

3️⃣ COPY + JSON（较少用）

COPY table_name FROM '/data/a.json' JSON;

十、生产最佳实践总结（重点）

✅ 本地导入用 \copy
✅ 永远显式写列名
✅ 不导入 ID（除非必要）
✅ 大批量导入前删除索引
✅ 导入后 ANALYZE

ANALYZE articles;

DateStyle

一、DateStyle 是什么？

DateStyle 决定 PostgreSQL 如何解析和显示日期/时间字符串。

SHOW DateStyle;

常见输出：

ISO, MDY

含义：

ISO：输出格式
MDY：解析顺序（Month-Day-Year）

二、SET DateStyle 的基本用法

1️⃣ 当前会话生效（最常用）

SET DateStyle = 'ISO, YMD';

或：

SET DateStyle TO 'ISO, YMD';

只影响 当前连接 / 当前事务。

2️⃣ 当前事务内生效

BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
-- 在这里执行 COPY / INSERT
COMMIT;

事务结束自动恢复
非常适合一次性导入数据

3️⃣ 数据库级（⚠️ 谨慎）

ALTER DATABASE mydb
SET DateStyle = 'ISO, YMD';

新连接生效。

4️⃣ 系统级（⚠️ 不推荐）

# postgresql.conf
DateStyle = 'ISO, YMD'

需要重启。

三、DateStyle 组成详解

格式：

DateStyle = '输出格式, 输入解析顺序'

输出格式（第一部分）

值	示例
ISO	2025-12-10 10:54:37
SQL	12/10/2025 10:54:37
Postgres	Wed Dec 10 10:54:37 2025
German	10.12.2025 10:54:37

输入解析顺序（第二部分）

值	含义
MDY	月-日-年
DMY	日-月-年
YMD	年-月-日

📌 只影响“有歧义”的日期字符串

❗️ 推荐：ISO + YMD

四、哪些格式会受 DateStyle 影响？

❌ 会受影响（危险）

10-12-2025
12/10/2025

MDY -> 12 月 10 日
DMY -> 10 月 12 日

✅ 不受影响（安全）

2025-12-10
2025-12-10 10:54:37
2025-12-10T10:54:37

ISO 8601 永远安全

五、SET DateStyle + COPY 的正确姿势

场景：CSV 时间是 10/12/2025 10:54:37

正确写法：

BEGIN;

SET LOCAL DateStyle = 'ISO, DMY';

\copy articles FROM '/tmp/articles.csv' WITH (FORMAT csv);

COMMIT;

只影响当前导入
不污染其他会话

六、什么时候不该用 SET DateStyle

❌ 长期依赖 DateStyle
❌ 程序代码里隐式修改
❌ 多人共享数据库
❌ 自动化任务中

原因：不可预期 + 易出事故

七、替代方案（更推荐）

用 to_timestamp() 明确格式

to_timestamp('10/12/2025 10:54:37', 'YYYY-MM-DD HH24:MI:SS')

或先导 TEXT 再转换（最稳）

八、总结

DateStyle 是“最后手段”，不是长期方案

ISO + YMD 时间格式才是正解

只在“历史脏数据”导入时用 SET LOCAL

DateStyle（扩展）

一、核心结论（先记住）

COPY 本身不定义时间格式

👉 时间格式由 PostgreSQL 的 DateStyle / 字段类型决定

也就是说：

COPY 只是“搬数据”
解析时间的是 PostgreSQL 类型系统

二、最安全、通用的时间格式（强烈推荐）

✅ TIMESTAMP / TIMESTAMPTZ

YYYY-MM-DD HH:MI:SS

示例：

2025-12-10 10:54:37
2025-12-10 10:54:37.052

永远不会出错
不受 DateStyle 影响
所有 PostgreSQL 版本通用

三、COPY 导入时间的正确示例

表结构

datetime TIMESTAMP NOT NULL

CSV 示例

datetime
2025-12-10 10:54:37
2025-12-10 10:54:37.052

COPY

\copy articles (datetime)
FROM 'articles.csv'
WITH (FORMAT csv, HEADER true);

四、常见“能成功 / 会失败”的时间格式

✅ 能成功（默认 DateStyle = ISO）

格式	示例
ISO	2025-12-10 10:54:37
带毫秒	2025-12-10 10:54:37.052
T 分隔	2025-12-10T10:54:37
UTC	2025-12-10T10:54:37Z

❌ 常见失败格式

格式	原因
2025/12/10 10:54:37	DateStyle 不兼容
2025年12月10日 10:54:37	非标准
10-12-2025 10:54:37	月日歧义
20251210	非 timestamp

报错通常是：

ERROR: invalid input syntax for type timestamp

五、如果你的 CSV 时间格式“不标准”，怎么办？

方案 1（最佳）：预处理 CSV（推荐）

把时间统一成：

YYYY-MM-DD HH:MI:SS

方案 2：先导入为 TEXT，再转换

CREATE TEMP TABLE tmp_articles (
    datetime TEXT,
    ...
);

\copy tmp_articles FROM 'articles.csv' WITH (FORMAT csv);

INSERT INTO articles (datetime, ...)
SELECT
    to_timestamp(datetime, 'YYYY-MM-DD HH24:MI:SS'),
    ...
FROM tmp_articles;

方案 3：临时调整 DateStyle（⚠️ 不推荐）

SET DateStyle = 'ISO, MDY';

⚠️ 风险：

影响当前会话
不利于自动化
容易出事故

六、TIMESTAMPTZ 特别说明（重要）

如果字段是：

datetime TIMESTAMPTZ

CSV 可写：

2025-12-10 10:54:37+08
2025-12-10T02:54:37Z

PostgreSQL 会：

自动转换为 UTC
内部统一存储

七、COPY + NULL 时间的写法

CSV：

datetime
\N

COPY：

\copy articles FROM 'articles.csv'
WITH (FORMAT csv, NULL '\N');

八、生产环境最佳实践

表字段用 TIMESTAMP 或 TIMESTAMPTZ
CSV 时间统一成 ISO 8601
不在 COPY 时依赖 DateStyle
导入失败先导 TEXT 再转换
永远不要导入“中文时间”

九、总结

COPY 不关心时间格式，PostgreSQL 类型才关心

👉 统一用 YYYY-MM-DD HH:MI:SS 永远不会踩坑

QUOTE

使用 Navicat 导出 CSV 文件时，字段数据都是使用 " " 括号括起来，而 PostgreSQL 的 CSV 文件则没有 " " 括号，梳理原因和正确用法。

一、COPY TO CSV 的基本语法

COPY articles TO '/path/to/file.csv'
WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    QUOTE '"',
    ESCAPE '"'
);

DELIMITER -> 字段分隔符
QUOTE -> 字段包裹符（默认为 “）
ESCAPE -> 转义符（默认为 “）

注意：COPY TO 时只有在字段中出现 分隔符、换行符或引用符 时才会使用 QUOTE 包裹字段。

二、为什么 QUOTE ‘”’ 没有生效？

1. 字段中没有需要引用的字符

字段内容：hello, world

CSV 默认规则：
字段中 没有分隔符、换行或引号 -> 不会加 " "
所以即使你设置 QUOTE ‘"’，输出也是：

hello,world

只有出现这些特殊字符，才会自动加引号：

字段内容：hello, "weixin"
输出： "hello, ""weixin"""

2. 误解了 QUOTE 的作用

QUOTE 并不是“每个字段都加引号”，而是“必要时自动加引号”
如果想每个字段都加引号，PostgreSQL 原生 COPY 没有直接参数，需要先处理数据或用客户端工具

三、如果想强制每个字段都加引号的解决方案

方案 1：在 SELECT 时手动加引号

COPY (
    SELECT '"' || REPLACE(title, '"', '""') || '"' AS title,
           '"' || REPLACE(content, '"', '""') || '"' AS content
    FROM articles
) TO '/path/to/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

REPLACE(…, ‘”’, ‘””’) -> 转义引号
每个字段都会被包裹双引号

方案 2：使用 psql \copy + 客户端处理（更推荐）

\copy (SELECT * FROM articles) TO 'articles.csv' CSV HEADER

由客户端 psql 生成 CSV
默认遵循 CSV 标准，遇到特殊字符自动加引号
可以结合 COPY TO PROGRAM ‘awk …’ 做自定义

四、总结

QUOTE ‘"’ 只在必要时加引号，字段没有特殊字符就不会显示
想强制加引号 -> 在 SELECT 里拼接 " " 或用客户端工具处理
COPY TO CSV 本身严格遵守 CSV 标准，并不是“每个字段都加引号”

分隔符：双引号

在 PostgreSQL COPY 中，双引号 " 不是分隔符（delimiter），而是 文本引用符（QUOTE）。

一、核心结论（先记住）

选项	含义
DELIMITER	字段分隔符（如 ,、\t、`
QUOTE	字段包裹符（默认是 “）
ESCAPE	转义符（默认也是 “）

👉 双引号 " 不能当 delimiter 使用（CSV 规范不允许）

二、标准 CSV 中 " " 的真实作用

例如一行 CSV：

1,"hello,world","a ""quoted"" text"

解析规则：

, -> delimiter
“hello,world” -> 一个字段（即使内部有逗号）
"” -> 表示一个 "

三、正确的 COPY 写法（CSV）

✅ 默认（推荐）

COPY articles
FROM STDIN
WITH (
    FORMAT csv,
    HEADER true
);

等价于：

DELIMITER ','
QUOTE '"'
ESCAPE '"'

✅ 显式指定（更清晰）

COPY articles
FROM STDIN
WITH (
    FORMAT csv,
    DELIMITER ',',
    QUOTE '"',
    ESCAPE '"',
    HEADER true
);

四、如果你的文件是 “|” 分隔（不是 CSV）

例如：

1|"hello world"|abc

正确写法：

COPY articles
FROM STDIN
WITH (
    FORMAT text,
    DELIMITER '|'
);

⚠️ 注意：

FORMAT text 下 没有 QUOTE 语义
" " 只是普通字符

五、如果你真的写成这样（❌ 错误）

COPY articles
FROM STDIN
WITH (DELIMITER '"');

问题：

PostgreSQL 语法允许
但几乎 不可用
因为 " 本身用于标识字段边界
会导致字段无法正确解析

👉 强烈不建议

六、几种常见文件 -> COPY 对照表

文件示例	COPY 配置
1,abc,def	FORMAT csv
1,“a,b”,c	FORMAT csv
`1	abc
1\tabc\tdef	FORMAT text, DELIMITER E’\t’
1;“abc”;“def”	FORMAT csv, DELIMITER ‘;’

七、这个问题最可能的真实需求是？

问的是：

COPY delimiter 双引号 “

通常背后真实需求是：

1️⃣ CSV 文件里有双引号

👉 不用管，默认就支持

2️⃣ 想“忽略双引号”

👉 不可能，CSV 规范必须用 QUOTE

3️⃣ 文件是 " 分隔

👉 建议先转换文件（不推荐）

八、总结

在 PostgreSQL COPY 中：

✅ " 是 QUOTE
❌ 不是 DELIMITER
✅ CSV 99% 情况下用默认配置

实践

导出：

-- 登录数据库
-- psql postgresql://username:password@host/database

-- 显示日期格式
SHOW DateStyle;

--  DateStyle
-- -----------
--  ISO, MDY
-- (1 row)

-- 导出
BEGIN;

-- 日期格式: ISO, YMD
SET LOCAL DateStyle = 'ISO, YMD';

-- 包含列名
\copy articles TO '/tmp/articles.csv' WITH (FORMAT csv, HEADER true);

-- 表字段：id, datetime, code, source, title, url
-- 如果 id 是自增主键，并无实际的意义，可以忽略 id
\copy articles (datetime, code, source, title, url) TO '/tmp/articles.csv' WITH (FORMAT csv, HEADER true);

COMMIT;

导入：

-- 显示日期格式
SHOW DateStyle;

--  DateStyle
-- -----------
--  ISO, MDY
-- (1 row)

-- 导入
-- 检查文件内容，如果用 Navicat 导出的数据，日期格式默认是 ISO, DMY
BEGIN;
SET LOCAL DateStyle = 'ISO, DMY';
\copy articles FROM '/tmp/articles.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 重置序列
SELECT setval(
    pg_get_serial_sequence('articles', 'id'),
    (SELECT MAX(id) FROM articles)
);

PostgreSQL COPY

一、COPY 是什么？

二、COPY 基本语法

1️⃣ 从文件导入表

2️⃣ 从表导出文件

3️⃣ 指定列（最常用）

三、COPY 的常用参数（非常重要）

时间 & 空值 🌟

四、COPY vs \copy（99% 的坑在这）

客户端导入（推荐）

五、IDENTITY / 自增字段 与 COPY（你刚踩的坑）

❌ 错误场景

✅ 正确方式 1（推荐）

✅ 正确方式 2（保留 id）

六、COPY 性能优化（生产级）

🚀 1️⃣ 关闭同步写（导入期间）

🚀 2️⃣ 临时删除索引（大表）

🚀 3️⃣ 使用 UNLOGGED 表（中间表）

🚀 4️⃣ 批量导入百万行建议参数

七、COPY + 事务行为（重要）

八、COPY 常见错误 & 解决方案

❌ 路径不存在

❌ 权限不足

❌ 编码错误

❌ CSV 列错位

九、COPY 高级用法

1️⃣ COPY 查询结果

2️⃣ COPY + ON CONFLICT（间接）

3️⃣ COPY + JSON（较少用）

十、生产最佳实践总结（重点）

DateStyle

一、DateStyle 是什么？

二、SET DateStyle 的基本用法

1️⃣ 当前会话生效（最常用）

2️⃣ 当前事务内生效

3️⃣ 数据库级（⚠️ 谨慎）

4️⃣ 系统级（⚠️ 不推荐）

三、DateStyle 组成详解

四、哪些格式会受 DateStyle 影响？

❌ 会受影响（危险）

✅ 不受影响（安全）

五、SET DateStyle + COPY 的正确姿势

六、什么时候不该用 SET DateStyle

七、替代方案（更推荐）

八、总结

DateStyle（扩展）

一、核心结论（先记住）

二、最安全、通用的时间格式（强烈推荐）

三、COPY 导入时间的正确示例

四、常见“能成功 / 会失败”的时间格式

五、如果你的 CSV 时间格式“不标准”，怎么办？

方案 1（最佳）：预处理 CSV（推荐）

方案 2：先导入为 TEXT，再转换

方案 3：临时调整 DateStyle（⚠️ 不推荐）

六、TIMESTAMPTZ 特别说明（重要）

七、COPY + NULL 时间的写法

八、生产环境最佳实践

九、总结

QUOTE

一、COPY TO CSV 的基本语法

二、为什么 QUOTE ‘”’ 没有生效？

1. 字段中没有需要引用的字符

2. 误解了 QUOTE 的作用

三、如果想强制每个字段都加引号的解决方案

方案 1：在 SELECT 时手动加引号

方案 2：使用 psql \copy + 客户端处理（更推荐）

四、总结

分隔符：双引号

一、核心结论（先记住）

二、标准 CSV 中 " " 的真实作用

三、正确的 COPY 写法（CSV）

✅ 默认（推荐）

✅ 显式指定（更清晰）

四、如果你的文件是 “|” 分隔（不是 CSV）

五、如果你真的写成这样（❌ 错误）

六、几种常见文件 -> COPY 对照表

七、这个问题最可能的真实需求是？

1️⃣ CSV 文件里有双引号

2️⃣ 想“忽略双引号”

3️⃣ 文件是 " 分隔

五、IDENTITY / 自增字段与 COPY（你刚踩的坑）