postgresql中COPY的用法

3tere

一.测试创建表：
[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)

海那些事_244

顶一下