批量导入时设置分隔符和数据格式

当数据来自不同系统时，格式可能都不一样。有的文件用逗号做分隔符，有的用分号，还有的直接用 tab（制表符）来分列。如果导入数据时分隔符没设置对，很容易出错或者数据被错误解析。

而且，现实中你还会遇到需要导入没有标准 CSV 表头的文本数据，还得处理空值，甚至有时候空行要被当成 NULL。所以，设置分隔符和格式其实是批量导入数据时不可缺少的一步。

常见分隔符：怎么搞？

在 PostgreSQL 里，COPY 命令可以很灵活地设置分隔符。咱们来看看怎么用。

设置分隔符

默认情况下，COPY 命令认为 CSV 文件里的列是用逗号分开的。但这其实不总是方便的：有的人用分号（;），有的人用竖线（|），甚至直接用 tab（\t）。

你可以用 DELIMITER 参数来指定分隔符，比如：

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

如果用的是分号：

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

甚至可以导入用 tab 分隔的文件：

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

这里 E'\t' 就是告诉 PostgreSQL 用 tab 做分隔符。

导入用非常规分隔符的文件

实战例子：你有个课程数据文件，用竖线（|）做分隔符。文件内容长这样：

course_id|course_name|credits
1|SQL Basics|3
2|Advanced SQL|4
3|PostgreSQL Masterclass|5

你可以这样把这些数据导入 courses 表：

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

这里我们直接告诉 PostgreSQL，分隔符是竖线。

导入时设置数据格式

分隔符只是其中一部分，文件里的数据格式也很重要。下面说说怎么设置数据格式。

忽略空行和设置 NULL

大数据文件里经常会有空行或者某些列没数据。PostgreSQL 默认把这些当成空字符串，除非你另有说明。要让这些值被当成 NULL，可以用 NULL AS 参数：

举个例子，假如你的文件里有空值：

id,first_name,last_name,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

导入时把 email 列的空值当成 NULL：

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

这样，文件里的空值就会在表里变成 NULL。

忽略空行

有时候文件里会有空行，这些其实没必要导入。PostgreSQL 可以自动忽略这些空行。

比如，文件里有空行：

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

用 IGNORE_BLANK_LINES 参数：

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

这样空行就不会被导入了。

处理非常规数据格式

有时候你要导入的不是 CSV，而是纯文本，比如用竖线 | 分隔，没有表头。

比如文件内容：

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

这种情况可以这样写：

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

如果文件没有表头，直接去掉 HEADER 参数就行。

数据格式设置实战例子

场景：你有个 grades.tsv 文件，里面是学生成绩。数据长这样：

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

你需要：

1. 导入文件，并把空值正确当成 NULL。
2. 确保数据导入没问题。

解决方案：

1. 先建个 grades 表：

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. 从文件导入数据：

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. 查查导入的数据：

SELECT * FROM grades;

预期结果：

建议和常见错误

错误：分隔符不对。 如果你没指定对的分隔符，PostgreSQL 要么报错，要么数据导入错位。比如文件用分号分隔，你忘了写 DELIMITER ';'，那 COPY 会把整行当成一列。

错误：NULL 解析不对。 如果没写 NULL AS ''，文件里的空值会被当成空字符串，这样后面做计算或筛选时很容易出错。

错误：数据格式不对。 分隔符没设置好，或者文件里本身有问题（比如用 tab 但你写成空格），就会报错，比如：ERROR: null value in column violates not-null constraint。