示例

1. 数据准备。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

   gaussdb=# create database db1 encoding='UTF-8' LC_COLLATE='en_US.UTF-8' LC_CTYPE ='en_US.UTF-8' dbcompatibility = 'A'; CREATE DATABASE gaussdb=# \c db1 Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "db1" as user "omm". db1=# CREATE TABLE test_copy(id int, name text); CREATE TABLE db1=# insert into test_copy values(1, 'aaa'); INSERT 0 1 db1=# insert into test_copy values(2, e'bb\nb'); INSERT 0 1 db1=# insert into test_copy values(3, e'cc\tc'); INSERT 0 1 db1=# insert into test_copy(name) values('ddd'); INSERT 0 1 db1=# insert into test_copy values(5, e'ee\\e'); INSERT 0 1 db1=# insert into test_copy values(6, ','); INSERT 0 1 db1=# insert into test_copy values(7, '"'); INSERT 0 1 db1=# SELECT * FROM test_copy; id | name ----+----------- 1 | aaa 2 | bb + | b 3 | cc c | ddd 5 | ee\e 6 | , 7 | " (7 rows)

2. 整表数据导出。

   1 2	db1=# copy test_copy to '/home/xy/test.csv' delimiter ',' quote '"' escape '"' encoding 'UTF-8' csv; COPY 7

   导出的 CS V文件内容如下所示：

   1 2 3 4 5 6 7 8

   1,aaa 2,"bb b" 3,cc c ,ddd 5,ee\e 6,"," 7,""""

3. 数据导入。

   db1=# truncate test_copy;
   TRUNCATE TABLE
   db1=# copy test_copy from '/home/xy/test.csv' delimiter ',' quote '"' escape '"' encoding 'UTF-8' csv;
   COPY 7

4. 自定义数据集导出：导出test_copy中id不为空所有行的name列。

   1 2	db1=# copy (select name from test_copy where id is not null) to '/home/xy/test.csv' delimiter ',' quote '"' escape '"' encoding 'UTF-8' csv; COPY 6

   导出的CSV文件内容如下所示：

   1 2 3 4 5 6 7

   aaa "bb b" cc c ee\e "," """"