Usage of copy in PostgreSQL

I. Test creation table:

[email protected] ~]$ cat Test.sql

CREATE TABLE weather (city varchar), Temp_lo int,

Temp_hi int, PRCP real,date date);

Two. Import the Test.sql file generation table:

Testdb01=> \i Test.sql

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

CREATE TABLE weather (city varchar), Temp_lo int,

Temp_hi int, PRCP real,date date);

(Press Return to proceed or enter X and return to cancel) ********************

CREATE TABLE

Three. Insert Data test:

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

Four. Basic syntax for copy:

Copy works by copying data between the data table and the file.

The syntax for copy in PostgreSQL is (from the document):

1. Copy the data from the file to the table:

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

from {' filename ' | Program ' Command ' | STDIN}

[[With] (option [, ...])]

2.copy the data from the table to the file:

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

to{' filename ' | Program ' Command ' | STDOUT}

[[With] (option [, ...])]

Where option is set the parameters are as follows:

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 '

Parameter interpretation and example of 3.Copy

FORMAT: Refers to the file type copied to a file, such as: Csv,text.

OIDS: Refers to the OID when copied to a file, but an error occurs when a table does not have an OID.

FREEZE: Freezes the data and then performs a vacuum FREEZE.

DELIMITER: Refers to the delimiter specified when exporting a file with single quotation marks. The default is Tab,csv file when text is comma by default. Binary file format is not supported.

HEADER: Refers to the name of the table field when copying to a file.

NULL: Specifies a null value, which defaults to \ n.

ENCODING: Specifies the encoding of the file and, if not specified, uses the client's character set by default.

STDIN: Refers to the input stream of the client program.

STDOUT: Point is the output stream of the client.

when you execute copy from, TABLE_NAME requires a table that actually exists, where the fields are optional, such as:

1. Copyemp (ename) from "E://emp.txt"

It is important to note that the field type is matched and that the file is as long as the value of a field.

2. Copyemp from "E://emp.txt"

The file needs to contain the values of the fields in the EMP table, or tab, or ', ', and so on, split the data

some notes, explanations, and examples when performing copy to:

1. Copyemp to STDOUT (DELIMITER ' | ')

Refers to the output at the client and with ' | ' As a delimiter

2. COPY (select* from EMP) to ' e://emp.csv ' (FORMAT ' csv ', DELIMITER ' | ', HEADER true,null ' ")

TABLE_NAME is a dynamic view, and arguments can contain more than one argument in parentheses, separated by commas. The value of herder can make true,false,1,0,on,off, it is important to note that the Herder parameter only takes effect when format is CSV.

3. COPY empto program ' Zip > E://emp.zip '

Parameter program refers to using the internal operating system to process the output file, the role of the above is to export and compress the EMP.

The copy operation can be performed not only on the command line, but also in the IDE's tools, such as its own pgadmin3.

Five. Test Demo:

1. Export the data in the table to a text file:

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

COPY 7

Or:

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 Francisco0.251994-11-27

China 01994-12-10

China0111994-12-11

China0211994-12-12

China0321994-12-13

China0421994-12-14

China0511994-12-15

[Email protected] ~]$ CAT/HOME/POSTGRES/WEATHER.TXT1

San Francisco0.251994-11-27

China 01994-12-10

China0111994-12-11

China0211994-12-12

China0321994-12-13

China0421994-12-14

China0511994-12-15

2. Specify the delimiter to export the table data to the CSV file:

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. Specify the delimiter to export the table data to the text Format file:

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. Import TXT file data into a database table (note that when importing data from a text file to a database, you must specify style symbols and text types, otherwise the import data fails with an error)

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

COPY 14

testdb01=#

testdb01=# Select COUNT (*) from weather;

Count

-------

28

(1 row)

Reference post: http://blog.csdn.net/chuan_day/article/details/44099859

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1976410

Usage of copy in PostgreSQL