在上篇文章给各人介绍了如何将postgresql数据库表内数据导出为excel格式(保举) 感爱好的朋友点击查察。
本文主要用于介绍如何使用copy大概\copy命令将postgresql数据库内表的数据导出为excel格式，方便用户查察编辑。
copy命令同\copy命令语法上相同，区别在于copy必须使用能够超级用户使用,copy … to file 中的文件都是数据库服务器所在的服务器上的文件，而\copy 一样平常用户即可执行且\copy 保存大概读取的文件是在客户端所在的服务器。本文主要以copy命令作为介绍重点，使用copy命令将表内数据倒为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、多场景使用介绍
①查察现有表数据- 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数据库表内数据导出为excel格式的文章就介绍到这了,更多干系postgresq表内数据导出excel格式内容请搜索脚本之家从前的文章或继续欣赏下面的干系文章希望各人以后多多支持脚本之家！ |
