Importing and Exporting Data with Extreme Performance
When there are strict demands for data import and export performance, and the import and export occur between database instances of the same version, you can use the BINARY format to read and store data as binary numbers other than regular text. Although this format provides performance advantages over others, it comes with the following limitations:
- The BINARY format is specific to GaussDB, making it non-portable. It is advisable to use this format only for importing and exporting data between databases of the same version.
- The BINARY format is tightly coupled with specific data types. For example, while the TEXT format allows exporting data from a smallint field and importing it into an integer column, this is not possible with the BINARY format.
- Certain data types cannot be imported or exported using the BINARY format. For details, see BINARY limitations.
Recommended export command:
1 2 3 4 |
set client_encoding = '{server_encoding}'; copy {data_source} to '/path/export.bin' binary; -- data_source can be a table name or a SELECT statement. -- server_encoding can be obtained using SHOW server_encoding. |
Corresponding import command:
1 2 3 4 |
set client_encoding = '{file_encoding}'; copy {data_destination} from '/path/export.bin' binary; -- data_destination can only be a table name. -- file_encoding indicates the encoding format used during binary file export. |

Before choosing the BINARY format, carefully review its limitations. Use it to enhance import and export performance only when you are completely certain that these limitations will not impact the data to be exported.
Examples
- 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
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(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 3 | cc c | ddd 5 | ee\e 6 | , 7 | " (6 rows)
- Export data.
1 2 3 4
db1=# set client_encoding = 'UTF-8'; SET db1=# COPY test_copy TO '/home/xy/test.bin' BINARY; COPY 6
- Import data.
db1=# truncate test_copy; TRUNCATE TABLE db1=# set client_encoding = 'UTF-8'; SET db1=# copy test_copy from '/home/xy/test.bin' BINARY; COPY 6
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot