如何將postgresql數(shù)據(jù)庫表內數(shù)據(jù)導出為excel格式(推薦)
在上篇文章給大家介紹了如何將postgresql數(shù)據(jù)庫表內數(shù)據(jù)導出為excel格式(推薦) 感興趣的朋友點擊查看。
本文主要用于介紹如何使用copy或者\copy命令將postgresql數(shù)據(jù)庫內表的數(shù)據(jù)導出為excel格式，方便用戶查看編輯。
copy命令同\copy命令語法上相同，區(qū)別在于copy必須使用能夠超級用戶使用,copy … to file 中的文件都是數(shù)據(jù)庫服務器所在的服務器上的文件，而\copy 一般用戶即可執(zhí)行且\copy 保存或者讀取的文件是在客戶端所在的服務器。本文主要以copy命令作為介紹重點，使用copy命令將表內數(shù)據(jù)倒為csv格式文件即為excel格式。
1、copy命令語法
COPY { 表名 [ ( 列名稱 [, ...] ) ] | ( 查詢 ) }
TO { '文件名' | PROGRAM '命令' | STDOUT }
[ [ WITH ] ( 選項 [, ...] ) ]
選項可以是下列內容之一
FORMAT 格式_名稱
FREEZE [ 布爾 ]
DELIMITER '分隔字符'
NULL '空字符串'
HEADER [ 布爾 ]
QUOTE '引用字符'
ESCAPE '轉義字符'
FORCE_QUOTE { ( 列名稱 [, ...] ) | * }
FORCE_NOT_NULL ( 列名稱 [, ...] )
FORCE_NULL ( 列名稱 [, ...] )
ENCODING 'encoding_name(編碼名)'
2、多場景使用介紹
①查看現(xiàn)有表數(shù)據(jù)
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,"""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"
③帶列名導出，指定使用|作為分隔符
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"",""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 null 'to be supplemented'; 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,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"
到此這篇關于如何將postgresql數(shù)據(jù)庫表內數(shù)據(jù)導出為excel格式的文章就介紹到這了,更多相關postgresq表內數(shù)據(jù)導出excel格式內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信