PostgreSQL----Copy between copy table and file

PostgreSQL provides a copy command for the mutual copy between the table and the file (and standard output, standard input), copy to the table to the file, and copy from the file to the table.

Example 1. Copy the entire table to standard output

test=# copy tbl_test1 to stdout;
1    HA    12
2    ha    543

Example 2. Copy some of the table's fields to the standard output and output the field names, using ', ' delimited between fields

test=# copy tbl_test1(a,b) to stdout delimiter ‘,‘ csv header;
a,b
1,HA
2,ha

Example 3. Copy the query results to standard output

test=# copy (select a,b from tbl_test1 except select e,f from tbl_test2 ) to stdout delimiter ‘,‘ quote ‘"‘ csv header;
a,b
2,ha

A few points to note when copying standard inputs to a table

1. The inter-field delimiter uses the "tab" key by default

2. Line break with enter key

3. End use of backslash + English stronghold (\.)

4. It is better to specify the order of the fields, otherwise it may be misplaced assignment

Example 4. Copying standard input into a table

test = # copy tbl_test1 (a, b, c) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 public princess
>> Long Live Long Live Long Live Long Live
>> \.
COPY 2
test = # select * from tbl_test1;
  a | b | c
--- + ------ + --------
  1 | HA | 12
  2 | ha | 543
  1 | Public | Princess
  2 | Long live | Long live
(4 rows)

Example 5. Copy from the standard input to the table, and the standard input first row as the field name (and the table does not matter, copy will automatically ignore the first row), the field delimiter is ', '

test = # copy tbl_test1 (a, b, c) from stdin delimiter ‘,’ csv header;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a, b, c
>> 3, hello
>> 4, superman, super
>> \.
COPY 2
test = # select * from tbl_test1;
  a | b | c
--- + ------ + --------
  1 | HA | 12
  2 | ha | 543
  1 | Public | Princess
  2 | Long live | Long live
  3 | Hello | hello
  4 | Superman | super
(6 rows)

The above is a copy of the table and the standard output and the standard input, and the copy of the table and the file is identical to the above, only the standard output and the standard input are converted into files. It is important to note that:

1. The database user must have write access to the path where the file resides.

2. If the table has Chinese characters, you need to set the encoding to GBK when exporting to the CSV file, otherwise it will be garbled if you use Excel to open it.

3. When importing a file into a table, you still need to consider the encoding problem

Example 6. Copying a table to a CSV file

test=# copy tbl_test1 to ‘/tmp/tbl_test1.csv‘ delimiter ‘,‘ csv header;
COPY 6

Use Excel to open file, Chinese display garbled

Example 7. Copy the table to a CSV file in GBK encoding

test=# copy tbl_test1 to ‘/tmp/tbl_test1.csv‘ delimiter ‘,‘ csv header encoding ‘GBK‘;
COPY 6

Open in Excel with normal Chinese display

Example 8. Copy the file you just exported to the table again, using the default encoding UTF8

test=# copy tbl_test1(a,b,c) from ‘/tmp/tbl_test1.csv‘ delimiter ‘,‘ csv header;
ERROR:  invalid byte sequence for encoding "UTF8": 0xb9 CONTEXT:  COPY tbl_test1, line 4

Example 9. Copy the file you just exported to the table again, using GBK encoding

test=# copy tbl_test1(a,b,c) from ‘/tmp/tbl_test1.csv‘ delimiter ‘,‘ csv header encoding ‘GBK‘;
COPY 6

PostgreSQL----Copy between copy table and file