本文主要用于介紹如何使用copy或者\copy命令將po(′▽?zhuān)?)stgresql數據庫內表的數據式數據導出為excel格式，方便用戶(hù)查看編輯。庫表

copy命令同\co??pyヽ(′ー｀)ノ命(╬?益?)令語(yǔ)法上相同，內數區別在于copy必須使用能夠超級用戶(hù)使用,據導copy … to file 中的文件都是數據庫服務(wù)器所在的服務(wù)器上的文件，而\copy 一般用戶(hù)即可執行且\copy 保存或者讀取的數據式文(wen)件是在客戶(hù)端所在的服務(wù)(wu)器。本文主要以??copy命令作為介紹ヾ(＾-＾)ノ重點(diǎn)，庫表使用copy命令將表內數據倒為csv格式文件即為excel格式。內數

1、據導co??py命(???)令語(yǔ)法

COPY { 表名 [ ( 列名稱(chēng) [,數據式 ...] ) ] | ( 查詢(xún) ) }
TO { '文件名' | PROGRAM '命令' | STDOUT }
[ [ WITH ] ( 選項 [, ...] ) ]

選項可以是下列內容之一
FORMAT 格式_名稱(chēng)
FREEZE [ 布爾 ]
DELIMITER '分隔字符'
NULL '空字符串'
HEADER [ 布爾 ]
QUOTE '引用字符'
ESCAPE '轉義字符'
FORCE_QUOTE { ( 列名稱(chēng) [, ...]?? ) | * }
FORCE_NOT_NULL ( 列名稱(chēng) [, ...] )
FORCE_NULL ( 列名稱(chēng) [, ...] )
ENCODING 'enc??oding_name(編碼名)'

2、多場(chǎng)景使用介紹

①查看現有表數據

test=# select * from test;
user_id | user_name | age | gender | remark
---------+---------------+-----+--------+------------??-----------------------------(′?｀*)-??----
1 | Jackie Chan | 45 | male | "police story",庫表"project A","rush hour"
3 | Brigitte Li | 46 | female |
4 | Maggie Cheung | 39 | female |
5 | Jet Li | 41 | male | "Fist of Legend","Once Upon a Time in China"
2 | Gong Li | 38 | female | "Farewell My Concubine","Lifetimes Living"
(5 行記錄)

②帶列名導出，默認情況下使用，內數作為分隔符

test=# copy test to '/tmp/test1.csv' with csv header;
COPY 5
test=# \! cat /tmp/test1.??csv
user_id,據導user_name,age,gender,remark
1,Jackie Chan,45,male,"""pol??icヽ(′▽?zhuān)?ノe story"",""project A"",""rush hour"""
3,Brigitte Li,46,female,
4,Maggie Cheung,39,female,
5,Jet Li,41,male,"""Fist of Legend"",""Once Upon a Time in China"""
2,Gong Li,38,female,"""Farewell My Concubine"",""Lifetimes Living"

③帶列名導出，指定使用|作為分隔符

test=# copy test to '/tmp/test1.csv' with csv header DELIMITER '|';
COPY 5
test=# \! cat /tmp/??test1.csv
user_id|user_name|age|gender|remark
1|Jackie Chan|45|male|"""police story"",數據式""project A"",""rush hour"""
3|Brigitte Li|46|female|
4|Maggie Cheung|39|female|
5|Jet Li|41|male|"""Fist of Legend"(′▽?zhuān)?",""Once Upon a Time in China"""
2|Gong Li|38|fe(′ω｀*)male|"""Farewell My Concubin??e"",""Lifetimes Living"

④帶列名導出，將空字符替換為指定值導出

test=# copy test to '/tmp/test1.csv'(?⊿?) with csv header null 'to be 庫表supplemented';
COPY 5
test=# \! cat /tmp/test1??.csv
user_id,user_name,age,gender,remark
1,Jackie Chan,45,male,"""po(′?ω?`)lice stor??y"",""project A"",""rush hour"""
3,Brigitte Li,46,female,to be supplemented
4,Maggie Cheung,39,female,to be supplemented
5,Jet(′?ω?`) Li,41,male,"""Fist of Legend"",""Once Upon a Time in China"""
2,Gong Li,38,female,"""Farewell My Concubine"",""Lifetimes(′?ω?`) Living"

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.n???et/article/206930.htm