Importing and Exporting Data When Only the TEXT Format Is Available

In TEXT format, each file is divided into multiple records using EOL characters. Each record is further split into multiple fields using delimiters, which are tab characters ('\t') by default.

Using the TEXT format necessitates special logic, for example:

• The backspace (0x08), form-feed (0x0C), newline (0x0A), carriage return (0x0D), horizontal tab (0x09), and vertical tab (0x0B) characters are escaped as '\b', '\f', '\n', '\r', '\t', and '\v', respectively.
• By default, the EOL character is configured as the first identified '\n', '\r', or '\r\n' during import, and as '\n' during export.
• A single backslash is escaped as double backslashes.
• NULL values are escaped as '\N'.

1
2
3

copy {data_source} to '/path/export.txt' eol e'\n' delimiter e'\t' encoding '{server_encoding}';
-- data_source can be a table name or a SELECT statement.
-- server_encoding can be obtained using SHOW server_encoding.

1
2
3

copy {data_destination} from '/path/export.txt' eol e'\n' delimiter e'\t' encoding '{file_encoding}';
-- data_destination can only be a table name.
-- file_encoding indicates the encoding format used during binary file export.

Example:

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

   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, e','); INSERT 0 1 db1=# insert into test_copy values(7, e'"'); INSERT 0 1 db1=# select * from test_copy; id | name ----+----------- 1 | aaa 3 | cc c | ddd 5 | ee\e 6 | , 7 | " (6 rows)

2. Export data.

   1 2	db1=# copy test_copy to '/home/xy/test.txt' eol e'\n' delimiter e'\t' encoding 'UTF-8'; COPY 6

3. Import data.

   db1=# truncate test_copy;
   TRUNCATE TABLE
   db1=# copy test_copy from '/home/xy/test.txt' eol e'\n' delimiter e'\t' encoding 'UTF-8';
   COPY 6

1
2
3

copy {data_source} to '/path/export.txt' without escaping eol e'\x1E' delimiter e'\x1F' null '\N' encoding '{server_encoding}';
-- data_source can be a table name or a SELECT statement.
-- server_encoding can be obtained using SHOW server_encoding.

The primary function of the escaping mechanism is to prevent special characters (such as delimiters and newline characters) in fields from damaging the file structure. When choosing to disable the escaping mechanism, be sure to isolate special characters by carefully choosing delimiters and newline characters for non-escaping scenarios and ensuring that these characters are absent in the data content. This is essential for non-escaped file parsing, as any character conflicts can lead to data parsing failure or structural disorder.

Example:

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

   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, e','); INSERT 0 1 db1=# insert into test_copy values(7, e'"'); INSERT 0 1 db1=# select * from test_copy; id | name ----+----------- 1 | aaa 3 | cc c | ddd 5 | ee\e 6 | , 7 | " (6 rows)

2. Export data.

   1 2	db1=# copy test_copy to '/home/xy/test.txt' without escaping eol e'\x1E' delimiter e'\x1F' null '\N' encoding 'UTF-8'; COPY 6

3. Read data. In this step, data is first split into rows based on the selected EOL character (0x1E). Then each row of data stream is further divided based on the delimiter (0x1F) to extract the field values within each row.

1. Select an EOL character. If the field data contains 0x0A, do not use 0x0A as the EOL character. Otherwise, newlines within the field data will be misinterpreted as EOL characters, resulting in a single line of data being split into two. To prevent this issue, ensure that the selected EOL character is not present in the field data. For instance, consider using 0x1E as the EOL character since it is not found in the field data. It is advisable to select from invisible characters (0x01 to 0x1F) for this purpose.
2. Select a delimiter. If the field data contains tab characters, do not use them as delimiters. Otherwise, tabs within the field data will be misinterpreted as delimiters, resulting in a single field being split into two. To prevent this issue, ensure that the selected delimiter is not present in the field data. For instance, consider using 0x1F as the delimiter since it is not found in the field data. It is advisable to select from invisible characters (0x01 to 0x1F) for this purpose.
3. Build data. Create a data file using the EOL character and delimiter selected in steps 1 and 2. Check the character set of the server. You need to generate a data file that matches the server's character set.
4. Import data. In this example, the character set of the data file is UTF-8.

   1 2	db1=# copy test_copy to '/home/xy/test.txt' without escaping eol e'\x1E' delimiter e'\x1F' null '\N' encoding 'UTF-8'; COPY 6