• 跳至主要導覽
  • 跳至主要內容
  • 跳至主要資訊欄
Office 指南

Office 指南

辦公室工作實用教學

  • Excel
  • Word
  • PowerPoint
  • Windows
  • PowerShell
  • R

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

分類:資料庫 標籤:PostgreSQL

讀者互動方式

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

主要資訊欄

搜尋

近期文章

  • C++ 語言使用 Crypto++ 實作 RSA 數位簽章教學與範例
  • C++ 語言使用 Crypto++ 實作 RSA-OAEP 搭配 SHA256 加密教學與範例
  • C++ 語言使用 Crypto++ 實作 AES 加密、解密、認證加密教學與範例
  • C++ 語言使用 Crypto++ 實作 MD5、SHA1、SHA2、BLAKE2 雜湊教學與範例
  • Ubuntu Linux 安裝、使用 Crypto++ 加密函式庫教學與範例
  • C 語言使用 OpenSSL 實作橢圓曲線 ECDH 金鑰交換教學與範例
  • Python 以 eciespy 實作 ECC 非對稱式加密方法教學與範例
  • C 語言使用 OpenSSL 實作 PBKDF2 教學與範例

推薦網站

  • Udemy 線上教學課程
  • Coursera 線上教學課程

關注本站

  • 電子郵件
  • Facebook

公益

  • 家扶基金會
  • 台灣世界展望會
  • Yahoo 奇摩公益
  • igiving 公益網
  • 兒福聯盟

Copyright © 2021 · Office Guide