postgresql中COPY的用法

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:

一.测试创建表:

[postgres@cacti ~]$ cat test.sql 

CREATE TABLE weather ( city varchar(80), temp_lo int, 

temp_hi int, prcp real,date date);


二.导入test.sql文件生成表:

testdb01=> \i test.sql

***(Single step mode: verify command)*******************************************

CREATE TABLE weather ( city varchar(80), temp_lo int, 

temp_hi int, prcp real,date date);

***(press return to proceed or enter x and return to cancel)********************

CREATE TABLE

三.插入数据测试:

testdb01=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China01', '43', '55', '1.0', '1994-12-11');

INSERT 0 1

testdb01=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China02', '44', '56', '1.0', '1994-12-12');

INSERT 0 1

testdb01=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China03', '45', '57', '2.0', '1994-12-13');

INSERT 0 1

testdb01=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China04', '46', '58', '2.0', '1994-12-14');

INSERT 0 1

testdb01=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('China05', '47', '59', '1.0', '1994-12-15');

INSERT 0 1


四.Copy的基本语法:

Copy的作用是复制数据在数据表和文件之间。

Copy在PostgreSql中的语法是(来自文档):

1、  将文件中的数据复制到表中:

COPY table_name [ ( column_name [, ...] ) ]

   FROM { 'filename' | PROGRAM 'command' | STDIN }

[ [ WITH ] ( option [, ...] ) ]

2、将表中的数据复制到文件中:

COPY { table_name [ ( column_name [, ...] )] | ( query ) }

    TO{ 'filename' | PROGRAM 'command' | STDOUT }

    [[ WITH ] ( option [, ...] ) ]

其中option的设置的参数如下:

   FORMAT format_name

   OIDS [ boolean ]

   FREEZE [ boolean ]

   DELIMITER 'delimiter_character'

   NULL 'null_string'

   HEADER [ boolean ]

   QUOTE 'quote_character'

   ESCAPE 'escape_character'

   FORCE_QUOTE { ( column_name [, ...] ) | * }

   FORCE_NOT_NULL ( column_name [, ...] )

   ENCODING 'encoding_name'

3.Copy的参数解释和示例

     FORMAT:指复制到文件的文件类型,如:CSV,TEXT。

       OIDS  :指复制到文件时带上oid,但是当某个表没有oid时就会出错。

       FREEZE :冻结数据,然后执行VACUUM FREEZE。

       DELIMITER:指在导出文件时的分隔符指定需要用单引号。在TEXT时默认为tab,CSV文件默认是逗号。不支持binary文件格式。

      HEADER:指在复制到文件时带上表字段名称。

       NULL:指定null值,默认为\N。

       ENCODING:指定文件的编码,如果没有指定就默认使用客户端的字符集。

       STDIN:指的是客户端程序的输入流。

STDOUT:指向是客户端的输出流。

 

  在执行COPY FROM时table_name就需要实际存在的表,其中字段是可以自选的,如:

1.         COPYemp(ename) FROM “E://emp.txt”

需要注意的是字段类型要匹配并且文件也只要一个字段的值。

2.         COPYemp FROM “E://emp.txt”

文件中需要包含emp表中的字段的值,或tab,或‘,’等分割开的数据

  在执行COPY TO时的一些注意,解释和示例:

1.         COPYemp TO STDOUT (DELIMITER ‘|’)

指的是输出在客户端并且以‘|’为分隔符

2.         COPY (select* from emp) TO ‘E://emp.csv’ (FORMAT ‘CSV’,DELIMITER ‘|’,HEADER true,NULL ‘’’’’’)

Table_name是可以为动态视图的,并且在后面的括号中参数可以包含多个,多个参数以逗号分隔开。HERDER的值可以使true,false,1,0,on,off,需要注意的是HERDER参数只有在FORMAT为CSV时生效。

3.         COPY empTO PROGRAM ‘zip > E://emp.zip’

参数PROGRAM指的是使用操作系统内部的程序对输出文件进行加工,上面的作用是将emp导出并且压缩。

 COPY操作其不仅仅在命令行中可以执行,在IDE的工具中也可以执行如其自带的pgadmin3。

五.测试演示:

1.导出表中的数据到文本文件:

testdb01=# COPY (select* from weather) TO '/home/postgres/weather.txt';

COPY 7

或者:

testdb01=# COPY  weather TO '/home/postgres/weather.txt1';

COPY 7

testdb01=# 

testdb01=# select* from weather;

     city      | temp_lo | temp_hi | prcp |    date    

---------------+---------+---------+------+------------

 San Francisco |      46 |      50 | 0.25 | 1994-11-27

 China         |      43 |      57 |    0 | 1994-12-10

 China01       |      43 |      55 |    1 | 1994-12-11

 China02       |      44 |      56 |    1 | 1994-12-12

 China03       |      45 |      57 |    2 | 1994-12-13

 China04       |      46 |      58 |    2 | 1994-12-14

 China05       |      47 |      59 |    1 | 1994-12-15

(7 rows)


[postgres@cacti ~]$ cat /home/postgres/weather.txt 

San Francisco 46 50 0.25 1994-11-27

China 43 57 0 1994-12-10

China01 43 55 1 1994-12-11

China02 44 56 1 1994-12-12

China03 45 57 2 1994-12-13

China04 46 58 2 1994-12-14

China05 47 59 1 1994-12-15

[postgres@cacti ~]$ cat /home/postgres/weather.txt1

San Francisco 46 50 0.25 1994-11-27

China 43 57 0 1994-12-10

China01 43 55 1 1994-12-11

China02 44 56 1 1994-12-12

China03 45 57 2 1994-12-13

China04 46 58 2 1994-12-14

China05 47 59 1 1994-12-15


2.指定分隔符导出表数据到csv文件:

testdb01=# COPY  weather  TO  '/home/postgres/weather.txt2' (FORMAT 'csv',DELIMITER '|') ;

COPY 7

[postgres@cacti ~]$ cat /home/postgres/weather.txt2

San Francisco|46|50|0.25|1994-11-27

China|43|57|0|1994-12-10

China01|43|55|1|1994-12-11

China02|44|56|1|1994-12-12

China03|45|57|2|1994-12-13

China04|46|58|2|1994-12-14

China05|47|59|1|1994-12-15

3.指定分隔符导出表数据到text格式文件:

 COPY  weather  TO  '/home/postgres/weather.txt5' (FORMAT 'text',DELIMITER '|') ;

COPY 14

[postgres@cacti ~]$ cat /home/postgres/weather.txt5

San Francisco|46|50|0.25|1994-11-27

China|43|57|0|1994-12-10

China01|43|55|1|1994-12-11

China02|44|56|1|1994-12-12

China03|45|57|2|1994-12-13

China04|46|58|2|1994-12-14

China05|47|59|1|1994-12-15

San Francisco|46|50|0.25|1994-11-27

China|43|57|0|1994-12-10

China01|43|55|1|1994-12-11

China02|44|56|1|1994-12-12

China03|45|57|2|1994-12-13

China04|46|58|2|1994-12-14

China05|47|59|1|1994-12-15

4.导入txt文件数据到数据库表中(注意在导入文本文件的数据到数据库时,必须指定风格符号和文本类型,否则导入数据失败,并且报错)

testdb01=# COPY  weather from  '/home/postgres/weather.txt5' (FORMAT 'text',DELIMITER '|');

COPY 14

testdb01=# 

testdb01=# select count(*) from weather;

 count 

-------

    28

(1 row)

参考博文:http://blog.csdn.net/chuan_day/article/details/44099859



 本文转自 wjw555 51CTO博客,原文链接:http://blog.51cto.com/wujianwei/1976410

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
SQL 关系型数据库 Shell
解决Windows下Postgresql使用copy导入out文件报Permission denied的问题
解决Windows下Postgresql使用copy导入out文件报Permission denied的问题
143 0
解决Windows下Postgresql使用copy导入out文件报Permission denied的问题
|
SQL JSON 关系型数据库
postgresql 高级用法
postgresql 高级用法
|
SQL 存储 移动开发
PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)
360 0
 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
|
SQL 关系型数据库 数据库
3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)
292 0
|
SQL XML 移动开发
【学习资料】第14期快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法
大家好,这里是快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法
|
SQL 关系型数据库 PostgreSQL
PostgreSQL datediff 日期间隔(单位转换)兼容SQL用法
标签 PostgreSQL , datediff 背景 使用datediff,对时间或日期相减,得到的间隔,转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周...
13968 0
|
关系型数据库 数据库 PostgreSQL
Postgresql pg_dump&pg_restore用法
PostgreSQL提供的一个工具pg_dump,逻辑导出数据,生成sql文件或其他格式文件,pg_dump是一个客户端工具,可以远程或本地导出逻辑数据,恢复数据至导出时间点。pg_dump 一次只转储一个数据库, 并且不会转储有关角色或表空间的信息 (因为那些是群集范围而不是每个数据库)。
11172 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 IV. 客户端接口_第 33 章 libpq - C 库_33.9. COPY命令相关的函数
33.9. COPY命令相关的函数 33.9.1. 用于发送COPY数据的函数 33.9.2. 用于接收COPY数据的函数 33.9.3. 用于COPY的废弃函数 PostgreSQL中的COPY命令有用于libpq的对网络连接读出或者写入的选项。
1406 0