介紹如何在 PostgreSQL 資料庫中使用 COPY 指令匯出資料表或查詢結果至 CSV 檔案。
測試用資料
建立一張儲存學生成績用的 grades 資料表：
-- 建立 grades 資料表 CREATE TABLE grades ( id SERIAL PRIMARY KEY, -- 序號 name VARCHAR(255), -- 名字 score_a SMALLINT, -- 成績 A score_b SMALLINT, -- 成績 B score_c SMALLINT -- 成績 C );
插入一些學生成績的資料：
-- 插入測試資料 INSERT INTO grades (name, score_a, score_b, score_c) VALUES ('Jake', 99, 87, 84), ('Harry', 79, 81, 94), ('Robert', 92, 67, 54), ('Michael', 91, 97, 74);
查詢 grades 資料表中的資料：
-- 查詢 grades 資料表內容 SELECT * FROM grades;
id | name | score_a | score_b | score_c ----+---------+---------+---------+--------- 1 | Jake | 99 | 87 | 84 2 | Harry | 79 | 81 | 94 3 | Robert | 92 | 67 | 54 4 | Michael | 91 | 97 | 74 (4 rows)
這張表格中的每一筆資料就是每一位學生的三次考試成績。
匯出資料表
若要將 PostgreSQL 資料庫中的資料表匯出為 CSV 檔案，可以使用 COPY 指令。
例如若要將 grade 資料表匯出為 /tmp/grades.csv，則可執行：
-- 將 grades 資料表匯出為 CSV 檔案 COPY grades TO '/tmp/grades.csv' DELIMITER ',' CSV HEADER;
這裡的 DELIMITER ',' 是指定以逗號作為分隔字元，而 CSV HEADER 則代表以含有標題列的 CSV 格式匯出資料。
匯出的 CSV 檔案內容會像這樣：
id,name,score_a,score_b,score_c 1,Jake,99,87,84 2,Harry,79,81,94 3,Robert,92,67,54 4,Michael,91,97,74
匯出查詢結果
COPY 指令也可以用來將查詢的結果匯出為 CSV 檔案。
假設我們有興趣以下查詢的結果：
-- 根據三次成績的最高分來排序 SELECT * FROM grades ORDER BY GREATEST(score_a, score_b, score_c) DESC;
id | name | score_a | score_b | score_c ----+---------+---------+---------+--------- 1 | Jake | 99 | 87 | 84 4 | Michael | 91 | 97 | 74 2 | Harry | 79 | 81 | 94 3 | Robert | 92 | 67 | 54 (4 rows)
若要將上面的查詢結果匯出為 CSV 檔案，只要將查詢指令整個包起來，放進 COPY 的指令中即可：
-- 將查詢結果匯出為 CSV 檔案 COPY ( SELECT * FROM grades ORDER BY GREATEST(score_a, score_b, score_c) DESC ) TO '/tmp/query_result.csv' DELIMITER ',' CSV HEADER;
輸出的 CSV 檔案內容為：
id,name,score_a,score_b,score_c 1,Jake,99,87,84 4,Michael,91,97,74 2,Harry,79,81,94 3,Robert,92,67,54
壓縮匯出 CSV 檔
COPY 指令可以將資料導向至外部程式，我們可以藉由這樣的方式，將資料進行壓縮之後再儲存：
-- 將匯出的 CSV 資料進行 GZIP 壓縮後再儲存 COPY grades TO PROGRAM 'gzip > /tmp/grades.csv.gz' DELIMITER ',' CSV HEADER;
發佈留言