載入時設定分隔符號與資料格式

資料來自不同系統時，格式常常不一樣。有的檔案用逗號當分隔符，有的用分號，還有的直接用 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' 就是指定 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。