Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Best Practices/ Best Practices for Import and Export Using COPY/ Typical Scenarios/ Using the Recommended CSV Format
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Using the Recommended CSV Format

In CSV format, each file is divided into multiple records using newline characters. Each record is further split into multiple fields using delimiters, which are commas (',') by default. Each field can be enclosed within a pair of quote characters, which are double quotation marks ('"') by default. This eliminates the need to escape special characters such as EOL characters and delimiters within the field content. Furthermore, CSV is a widely recognized standard with exceptional cross-platform compatibility and cross-industry universality, solidifying its position as the top recommended format.

Export command:

1
2
3
 
-- data_source can be a table name or a SELECT statement.
-- server_encoding can be obtained using SHOW server_encoding.
COPY {data_source} TO '/path/export.csv' delimiter ',' quote '"' escape '"' ENCODING {server_encoding} CSV;

Import command:

1
2
3
 
-- data_destination can only be a table name.
-- file_encoding indicates the encoding format used during file export.
COPY {data_destination} FROM '/path/export.csv' delimiter ',' quote '"' escape '"' ENCODING {file_encoding} CSV;

To import a manually created CSV data file into the database, ensure that the file complies with CSV standards. Additionally, specify the correct parameters, including delimiters, quote characters, and EOL characters, in the COPY command.

Examples

  1. Prepare data. 

     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
34
35
36
    		 
    gaussdb=# CREATE DATABASE db1 ENCODING='UTF-8' LC_COLLATE='en_US.UTF-8' LC_CTYPE ='en_US.UTF-8' DBCOMPATIBILITY = 'ORA';
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);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
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. Export data from the entire table. 

    1
2
3
4
5
6
7
    		 
    db1=# COPY test_copy TO '/home/xy/test.csv' 
    DELIMITER ',' 
    QUOTE '"' 
    ESCAPE '"' 
    ENCODING 'UTF-8' 
    CSV;
COPY 7

    The content of the exported CSV file is as follows:

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

  3. Import data. 

    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. (Custom data set export) Export the name column for all rows in test_copy, excluding those with an empty ID. 

    1
2
3
4
5
6
7
8
    		 
    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

    The content of the exported CSV file is as follows:

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

Parent topic: Typical Scenarios