postgresql中COPY的用法

標籤：sql   postgre   copy   

一.測試建立表：

[[email protected] ~]$ 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)

[[email protected] ~]$ cat /home/postgres/weather.txt 

San Francisco46500.251994-11-27

China435701994-12-10

China01435511994-12-11

China02445611994-12-12

China03455721994-12-13

China04465821994-12-14

China05475911994-12-15

[[email protected] ~]$ cat /home/postgres/weather.txt1

San Francisco46500.251994-11-27

China435701994-12-10

China01435511994-12-11

China02445611994-12-12

China03455721994-12-13

China04465821994-12-14

China05475911994-12-15

2.指定分隔字元匯出表資料到csv檔案:

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

COPY 7

[[email protected] ~]$ 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

[[email protected] ~]$ 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

本文出自 “10931853” 部落格，請務必保留此出處http://wujianwei.blog.51cto.com/10931853/1976410

postgresql中COPY的用法