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.
Recommended export command:
1 2 3 4 |
COPY {data_source} FROM '/path/export.fixed' encoding {server_encoding} FIXED FORMATTER(col1_name(col1_offset, col1_length), col2_name(col2_offset, col2_length)); -- 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. |
Corresponding import command:
1 2 3 4 |
COPY {data_destination} from '/path/export.fixed' encoding {file_encoding} FIXED FORMATTER(col1_name(col1_offset, col1_length), col2_name(col2_offset, col2_length)); -- 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. |

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
- 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
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, '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)
- 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
- 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
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