概述
最近有需要對數據進行遷移的需求，主要是從excel表導入到PG數據庫表，這裡我是從postgres本身支持的copy命令來實現的，所以主要介紹一下COPY的命令..
官方文檔：http://postgres.cn/docs/11/sql-copy.html
一、copy命令
COPY在PostgreSQL表和文件之間交換數據。 COPY TO把一個表的所有內容都拷貝到一個文件，而COPY FROM從一個文件裡拷貝數據到一個表裡(把數據附加到表中已經存在的內容裡)。 COPY TO還能拷貝SELECT查詢的結果。
如果聲明瞭一個字段列表，COPY將只在文件和表之間拷貝已聲明字段的數據。 如果表中有任何不在字段列表裡的字段，那麼COPY FROM將為那些字段插入缺省值。
帶文件名的COPY指示PostgreSQL服務器直接從文件中讀寫數據。 如果聲明瞭文件名，那麼服務器必須可以訪問該文件，而且文件名必須從服務器的角度聲明。 如果使用了PROGRAM選項，則服務器會從指定的這個程序進行輸入或是寫入該程序作為輸出。 如果使用了STDIN 或STDOUT選項，那麼數據將通過客戶端和服務器之間的連接來傳輸。
copy命令可以操作的文件類型有：txt、sql、csv、壓縮文件、二進制格式
1、導出CSV命令
<code>COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]/<code>2、導入CSV命令
<code>COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]/<code>
3、常用參數說明
注意：
1）COPY只能用於表，不能用於視圖。當然也可以用於COPY (SELECT * FROM viewname) TO ...
2）COPY僅僅處理已指定的特定表；它將不復制數據到子表或從子表中複製數據。 因此比如COPY table TO顯示與SELECT * FROM ONLY table相同的數據。 但是COPY (SELECT * FROM table) TO ...可以用於轉儲在繼承層次結構的所有數據。
3）對任何要COPY TO出來的數據必須有查詢的權限，對任何要COPY FROM入數據的表必須有插入權限。 對列在命令中的字段擁有列權限也是必須的。
4）COPY命令裡面的文件必須是由服務器直接讀或寫的文件，而不是由客戶端應用讀寫。 因此，它們必須位於數據庫服務器上或者可以被數據庫服務器所訪問，而不是客戶端程序。 它們必須被運行PostgreSQL服務器的用戶可讀或寫，而不是客戶端程序。 由PROGRAM選項指定的命令必須是由服務器來執行的，而不是客戶端程序，必須是由PostgreSQL所屬的用戶。 COPY在指定一個程序或是命令時只允許數據庫超級用戶來執行，因為它允許讀寫任意服務器有權限訪問的文件。
5）不要混淆COPY和 psql應用程序中的\\copy指令。 \\copy調用COPY FROM STDIN或COPY TO STDOUT，然後把數據抓取/存儲到一個psql客戶端可以訪問的文件中。 因此，使用\\copy的時候，文件訪問權限是由客戶端應用程序而不是服務器端決定的。
6）COPY FROM在執行時會觸發目標表上所有觸發器和檢查約束。不過，不會執行規則。
二、實例
1、導出demo_employee_target表
<code>$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgres
\\dn --list schemas
set search_path to 'fslvis_schema'; --切換schema,默認public
\\dt --list tables
copy demo_employee_source to '/tmp/test.csv' with csv;
copy demo_employee_target to '/tmp/test.csv' with csv;/<code>
2、導出指定字段
<code>\\d demo_employee_target --查看錶結構
copy demo_employee_target(c_oid,c_code,c_operatetime) to '/tmp/test2.csv' with csv;/<code>
3、導出select語句
<code> COPY (select c_oid,c_code,c_operatetime,c_status from demo_employee_target) TO '/tmp/test3.csv' WITH csv;/<code>
4、導入test.csv
<code> COPY t1 FROM '/tmp/test.csv' WITH csv;/<code>
如果導出的時候，指定了header屬性，那麼在導入的時候，也需要指定：
<code> COPY t1 FROM '/tmp/test.csv' WITH csv header;/<code>
5、 copy命令導入導出數據為sql格式
<code>COPY t1 TO '/tmp/t1.sql';
COPY t1 FROM '/tmp/t1.sql';/<code>
6、將excel表中的數據導入到Postgresql數據庫的某張表中
步驟：
1)將excel表格字段，按照postgresql數據庫中表的字段順序來整理數據，並保存為csv文件。
2)用記事本打開csv文件，另存為UTF-8格式。
3)使用客戶端鏈接postgresql數據庫，執行如下腳本，導入csv文件到Postgresql數據表：
<code> copy t1 from 'd:/test/testdata.csv' delimiter as',' csv quote as '"'/<code>
注意:
1）test目錄需要賦予postgresql用戶可讀寫的權限，否則會有如下報錯信息：ERROR: could not open file "d:/testdata.csv" forwriting:Permission denied
2）csv文件要為utf-8格式，否則導入時可能會有報錯：ERROR:invalid bytesequence for encoding "UTF8": 0xcdf5
覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~
閱讀更多 波波說運維 的文章
關鍵字: Excel PostgreSQL 命令
