データロード時の区切り文字とデータフォーマットの設定

データがいろんなシステムから来ると、フォーマットがバラバラなことが多いよね。あるファイルはカンマ区切り、別のファイルはセミコロン、さらに別のやつはタブ区切りだったりする。区切り文字の設定をミスると、エラーが出たりデータが変な感じで解釈されちゃう。

しかも、現場だと標準的なCSVヘッダーがないテキストデータをロードしなきゃいけないこともあるし、空値の扱いとか、空行をNULLとして扱うケースも考えなきゃいけない。だから、区切り文字やフォーマットの設定は大量データロードの超重要なポイントなんだ。

基本の区切り文字：どうやって使う？

PostgreSQLのCOPYコマンドは、区切り文字の設定がめっちゃ柔軟。どうやるか見てみよう。

区切り文字の指定

デフォだとCOPYはCSVファイルのカラムをカンマで区切ってると思ってる。でも、いつもそうとは限らないよね。セミコロン（;）、パイプ（|）、タブ（\t）とか使う人もいる。

区切り文字をDELIMITERパラメータで指定する例：

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

もしセミコロン区切りだったら：

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

タブ区切りのファイルもいける：

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

ここでE'\t'はタブ文字が区切りって意味だよ。

変則的な区切り文字のファイルをロードする

実践例：コース情報のファイルがあって、パイプ（|）が区切り文字になってる。ファイルの中身はこんな感じ：

course_id|course_name|credits
1|SQLの基礎|3
2|上級SQL|4
3|PostgreSQLマスタークラス|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になる。

空行を無視する

たまにファイルに空行が混じってることがあるけど、そういうのはロードしなくてOK。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パラメータは外してOK。

データフォーマット設定の実践例

シナリオ：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は行全体を1カラムとして扱っちゃう。

ミス：NULLの解釈ミス。 NULL AS ''パラメータを指定しないと、ファイルの空値が空文字列として扱われて、計算やフィルタでバグることが多い。

ミス：データフォーマットの間違い。 区切り文字の設定ミスや、ファイル内でタブとスペースを間違えたりすると、ERROR: null value in column violates not-null constraintみたいなエラーになる。