用 PostgreSQL 的 COPY 匯入匯出 CSV

darkbaby123發表於2017-02-12
SQL

TL;DR

無意中看到了一篇講 資料批量匯入 的文章，才注意到 PostgreSQL 的 COPY 命令。簡而言之，它用來在檔案和資料庫之間複製資料，效率非常高，並且支援 CSV 。

匯出 CSV

以前做類似的事情都是用程式語言寫，比如用程式讀取資料庫的資料，然後用 CSV 模組寫入檔案，當資料量大的時候還要控制不要一次讀太多，比如一次讀 5000 條，處理完再讀 5000 條之類。

PostgreSQL 的 COPY TO 直接可以幹這個事情，而且匯出速度是非常快的。下面例子是把 products 表匯出成 CSV ：

COPY products
TO `/path/to/output.csv`
WITH csv;

可以匯出指定的屬性：

COPY products (name, price)
TO `/path/to/output.csv`
WITH csv;

也可以配合查詢語句，比如最常見的 SELECT ：

COPY (
  SELECT name, category_name
  FROM products
  LEFT JOIN categories ON categories.id = products.category_id
)
TO `/path/to/output.csv`
WITH csv;

匯入 CSV

跟上面的匯出差不多，只是把 TO 換成 FROM ，舉例：

COPY products
FROM `/path/to/input.csv`
WITH csv;

這個命令做匯入是非常高效的，在開頭那篇部落格作者的測試中，COPY 只花了 INSERT 方案 1/3 的時間，而後者還用 prepare statement 優化過。

總結

COPY 還有一些其他配置，比如把輸入輸出源指定成 STDIN/STDOUT 和 shell 命令，或者指定 CSV 的 header 等等。這裡不再贅述。資料庫也有很多細節可挖，有些簡單卻非常實用。合理使用能大大提高效率。

參考資料

Friends Don’t Let Friends Use Loops
PostgreSQL: COPY

相關文章