postgresql中COPY的用法

概述一.测试创建表： [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: ver

一.测试创建表：

[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),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,date) VALUES ('China02','44','56','1994-12-12');

INSERT 0 1

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

INSERT 0 1

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

INSERT 0 1

testdb01=# INSERT INTO weather (city,date) VALUES ('China05','47','59','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

总结

以上是内存溢出为你收集整理的postgresql中COPY的用法全部内容，希望文章能够帮你解决postgresql中COPY的用法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。