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

Exporting Files for Manual Parsing

The FIXED format maintains a fixed data structure to simplify file parsing: Each row represents a record, and within each row, the starting offset and length of each field value are fixed. Therefore, it is advisable to utilize the FIXED format when you need to manually parse a data file exported from the database. Despite this, the FIXED format has its limitations, as detailed in FIXED description.

Export command:

1
2
3
4
 
-- data_source can be a table name or a SELECT statement.
-- server_encoding can be obtained using SHOW server_encoding.
-- col1_name(col1_offset, col1_length) indicates that the data named col1_name in each row of the data file starts at the position with an offset of col1_offset and extends for a length of col1_length.
COPY {data_source} FROM '/path/export.fixed' encoding {server_encoding} FIXED FORMATTER(col1_name(col1_offset, col1_length), col2_name(col2_offset, col2_length));

Import command:

1
2
3
4
 
-- data_destination can only be a table name.
-- file_encoding indicates the encoding format used during file export.
-- col1_name(col1_offset, col1_length) indicates that the data named col1_name in each row of the data file starts at the position with an offset of col1_offset and extends for a length of col1_length.
COPY {data_destination} FROM '/path/export.fixed' ENCODING {file_encoding} FIXED FORMATTER(col1_name(col1_offset, col1_length), col2_name(col2_offset, col2_length));

The fixed column width format has limitations: It requires maintaining a relatively uniform width for each column in the table, allowing for the selection of an appropriate fixed col_length to prevent data truncation and eliminate unnecessary spaces in all columns. In cases where column widths vary significantly or change dynamically, it is advisable to prioritize flexible formats such as TEXT or CSV to ensure data integrity and efficient use of storage space.

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
    		 
    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, 'bb"b');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES(3, 'cc  c');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES('', e'dd\td');
INSERT 0 1
db1=# INSERT INTO test_copy VALUES('5', e'ee\e');
INSERT 0 1

db1=# SELECT * FROM test_copy;
 id |   name    
----+-----------
  1 | aaa
  2 | bb"b
  3 | cc  c
    | dd      d
  5 | eee
(5 rows)

  2. Export data. 

    1
2
    		 
    db1=# COPY test_copy TO '/home/xy/test.fixed' encoding 'UTF-8' FIXED FORMATTER(id(0,1), name(1,5));
COPY 5

    The content of the exported data file is as follows:

    1
2
3
4
5
    		 
    1  aaa
2 bb"b
3cc  c
  dd	d
5  eee

  3. Import data. 

    db1=# TRUNCATE test_copy;
TRUNCATE TABLE
db1=# COPY test_copy FROM '/home/xy/test.fixed' ENCODING 'UTF-8' FIXED FORMATTER(id(0,1), name(1,5));
COPY 5

Parent topic: Typical Scenarios