PostgreSQL 資料庫 COPY 匯入、匯出 CSV 表格資料教學與範例

介紹如何在 PostgreSQL 資料庫中使用 COPY 指令匯入與匯出 CSV 格式的資料表。

PostgreSQL 資料庫的 COPY 指令可以用來處理資料表與一般檔案之間的資料移動，例如將 CSV 檔案匯入資料表，或是將資料表中的資料匯出成 CSV 檔案等。

建立測試用資料

為了示範 COPY 指令的使用方式，我們先建立一張測試用的 products 資料表：

-- 建立 products 資料表
CREATE TABLE products (
  id serial PRIMARY KEY,
  name VARCHAR (255),
  price INTEGER,
  last_update DATE
);

接著建立一個 CSV 檔案 data.csv，內容如下：

name,price,last_update
A031,320,2015-10-14
C001,420,2013-03-08
B021,280,2013-12-02
Y158,690,2015-11-21
A202,195,2019-02-23
D022,540,2017-03-19

從 CSV 檔案匯入資料

若要將 data.csv 檔案中資料匯入 products 資料表，可以使用以下的 COPY 指令：

-- 從 CSV 檔案匯入資料
COPY products(name, price, last_update) FROM '/path/to/data.csv'
  DELIMITER ',' CSV HEADER;

這裡的 DELIMITER 是指定 CSV 格式所使用的分隔字元，而 HEADER 則是註明該 CSV 檔案含有標頭（若 CSV 檔案沒有標頭行的話，HEADER 就要拿掉）。

由於 COPY 不允許一般使用者從檔案匯入資料，所以如果不是管理者權限執行上述指令，就會出現這樣的錯誤訊息：

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

這時候最簡單的方式就是改用 \COPY 指令，其用法跟一般的 COPY 幾乎相同：

-- 從 CSV 檔案匯入資料
\COPY products(name, price, last_update) FROM '/path/to/data.csv'
  DELIMITER ',' CSV HEADER;

另外一種方式是讓 COPY 從 STDIN 讀取資料：

# 從 STDIN 匯入資料
psql -h MY_HOST -d MY_DB -U MY_USER -c \
  "COPY products(name, price, last_update) FROM STDIN DELIMITER ',' CSV HEADER" \
  < data.csv

成功匯入資料之後，查看匯入之後的資料：

-- 顯示所有資料
SELECT * FROM products;

 id | name | price | last_update
----+------+-------+-------------
  1 | A031 |   320 | 2015-10-14
  2 | C001 |   420 | 2013-03-08
  3 | B021 |   280 | 2013-12-02
  4 | Y158 |   690 | 2015-11-21
  5 | A202 |   195 | 2019-02-23
  6 | D022 |   540 | 2017-03-19
(6 rows)

將資料表匯出成 CSV 檔案

若要將資料表中的所有資料匯出成 CSV 檔案，可以使用以下 COPY 指令：

-- 匯出資料至 CSV 檔案
COPY products TO '/path/to/output.csv' DELIMITER ',' CSV HEADER;

這裡同樣只能使用管理者權限匯出 CSV 檔案，若是一般使用者，要改用 \COPY 指令：

-- 匯出資料至 CSV 檔案
\COPY products TO '/path/to/output.csv' DELIMITER ',' CSV HEADER;

或是將資料以 COPY 匯出至 STDOUT　之後，再導入檔案：

# 匯出資料至 STDOUT 再導入檔案
psql -h MY_HOST -d MY_DB -U MY_USER -c \
  "COPY products TO STDOUT DELIMITER ',' CSV HEADER" \
  > output.csv

參考資料：PostgreSQL Tutorial、PostgreSQL Tutorial