PostgreSQL / 系统课时

导出与导入 CSV 数据

导出与导入 CSV 数据

CSV 适合在数据库、脚本和电子表格之间交换表数据。本课将使用服务端 COPY 与客户端 \copy 完成导出和导入,并正确处理表头、分隔符、空值、编码和数据类型。

学习目标

  • 区分 COPY 与 \copy 的文件访问位置
  • 导出带表头的 UTF-8 CSV
  • 在导入前验证结构并定位坏行

选择 COPY 还是 \copy

COPY 由数据库服务器进程读写文件,路径位于服务器文件系统,并受到数据库服务账号权限限制。\copy 是 psql 元命令,数据经客户端传输,路径位于执行 psql 的机器上,日常导出更方便。

使用 \copy 导出 CSV

下面从 public.customers 导出指定列。明确列顺序可避免表结构变化后文件格式悄然改变。

bash
psql -h <HOST> -p <PORT> -U app_user -d appdb -c "\copy (select id, name, email, created_at from public.customers order by id) to 'customers.csv' with (format csv, header true, encoding 'UTF8')"
wc -l customers.csv
head -n 3 customers.csv

预期输出

text
COPY <COUNT>
<LINES> customers.csv
id,name,email,created_at
<ROW_1>
<ROW_2>

<LINES> 通常等于数据行数加一行表头。字段中若包含逗号、双引号或换行,CSV 会自动按规则引用,不能用简单字符串分割代替标准 CSV 解析器。

准备目标表

导入前先创建与 CSV 列顺序和类型一致的目标表。下面是完整示例,生产中应根据业务约束补充主键、唯一约束和索引。

sql
CREATE SCHEMA IF NOT EXISTS staging;

CREATE TABLE staging.customers_import (
    id bigint,
    name text,
    email text,
    created_at timestamptz
);

预期结果

text
创建 staging schema 和 customers_import 表,四列类型与 CSV 中的字段顺序对应。

使用 \copy 导入 CSV

导入时显式列出目标列,并声明表头与编码。命令失败会报告文件行号附近的错误信息。

bash
psql -h <HOST> -p <PORT> -U app_user -d appdb -v ON_ERROR_STOP=1 -c "\copy staging.customers_import (id, name, email, created_at) from 'customers.csv' with (format csv, header true, encoding 'UTF8')"

预期输出

text
COPY <COUNT>

<COUNT> 应等于导出的数据行数。导入完成后应检查数量、空值和重复键,再把数据合并到正式表。

bash
psql -h <HOST> -p <PORT> -U app_user -d appdb -c "select count(*) as rows, count(*) filter (where email is null) as null_emails from staging.customers_import;"

预期输出

text
 rows    | null_emails
---------+-------------
 <COUNT> | <COUNT>
(1 row)

处理分隔符与空值

当外部文件使用分号分隔或以特定文本表示空值时,应在两端写明相同规则。例如 delimiter ';' 和 null 'NULL'。不要把空字符串与 SQL NULL 混为一谈,它们在业务语义和约束检查中不同。

bash
psql -h <HOST> -p <PORT> -U app_user -d appdb -c "\copy staging.customers_import from 'customers-semicolon.csv' with (format csv, header true, delimiter ';', null 'NULL', encoding 'UTF8')"

预期输出

text
COPY <COUNT>

定位导入失败

典型错误包括列数不一致、非法日期、字符编码不匹配和唯一约束冲突。先把数据导入无约束的 staging 表,再用 SQL 做类型转换和业务校验,通常比直接写入正式表更容易定位问题。

对于来源不可信的 CSV,还应限制文件大小、检查公式注入风险,并使用受控目录保存,避免覆盖脚本或配置文件。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

导出与导入 CSV 数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通