Help Center/ GaussDB/ User Guide/ Database Migration/ Using the copy to/from Command to Export and Import Data

Updated on 2024-10-08 GMT+08:00

View PDF

Using the copy to/from Command to Export and Import Data

Scenarios

The gsql tool provides the \copy meta-command to import or export data. \copy applies only to small-scale data import in good format. It does not preprocess invalid characters or provide error tolerance. Therefore, \copy cannot be used in scenarios where abnormal data exists.

Preparing for Data Migration

Prepare an ECS or a device that can access the GaussDB instance over EIP.
- To connect to a GaussDB instance through an ECS, you must first create an ECS.
  For details on how to create and log in to an ECS, see Purchasing an ECS and Logging In to an ECS in Elastic Cloud Server Getting Started.
- To connect to a GaussDB instance through a device that can access the GaussDB instance over EIP, you must:
  1. Bind an EIP to the GaussDB instance. For details, see Binding an EIP.
  2. Ensure that the local device can access the EIP that has been bound to the GaussDB instance.
Install the gsql client on the prepared ECS or device that can access the GaussDB database, and connect it to the GaussDB database instance. For details, see Using gsql to Connect to a Database.

Exporting Data

On the prepared ECS or device that can access GaussDB, connect to the GaussDB instance and export the content of the copy_example table.

Method 1: Export the content of the copy_example table to stdout in CSV format. Use quotation marks (") as the quotes, and use the quotes to enclose the fourth and fifth columns.

\copy copy_example to stdout CSV quote as '"' force quote col_4,col_5;
1,iamtext,iamvarchar,"2006-07-07","12:00:00"
2,sometext,somevarchar,"2006-07-07","12:00:00"
3,sometext,somevarchar,"2006-07-07","12:00:00"
4,sometext,somevarchar,"2022-07-07","19:00:02"
5,sometext,somevarchar,"2006-07-07",
6,sometext,somevarchar,"2022-07-07","19:00:02"

Method 2: Export the content of the copy_example table to the copy_example.csv file under the local path /tmp/data/. Use vertical bars (|) as the delimiters and quotation marks (") as the quotes.

\copy copy_example to '/tmp/data/copy_example.csv' csv delimiter '|' quote '"';

Check the /tmp/data/copy_example.csv file to ensure that the data has been exported.

1|iamtext|iamvarchar|2006-07-07|12:00:00
2|sometext|somevarchar|2006-07-07|12:00:00
3|sometext|somevarchar|2006-07-07|12:00:00
4|sometext|somevarchar|2022-07-07|19:00:02
5|sometext|somevarchar|2006-07-07|
6|sometext|somevarchar|2022-07-07|19:00:02

Method 3: Export the query result set of the copy_example table to the copy_example2.csv file under the local path /tmp/data/ . Use commas (,) as the delimiters and quotation marks (") as the quotes.
```
\copy (select * from copy_example where col_1 = 1) to '/tmp/data/copy_example2.csv' csv delimiter ',' quote '"';
```
Check the /tmp/data/copy_example2.csv file to ensure that the data has been exported.
```
1,iamtext,iamvarchar,2006-07-07,12:00:00
```

Importing Data

Import data to a GaussDB instance. For example, import data to the target table copy_example, the structure is as follows:

      
           create table copy_example
(
	col_1 integer,
	col_2 text,
	col_3 varchar(12),
	col_4 date,
	col_5 time
);

On the prepared ECS or device that can access GaussDB, connect to the GaussDB instance and import data to the target table copy_example.

Method 1: Copy data from stdin and import data to the target table copy_example.

\copy copy_example from stdin csv;

When >> is displayed, enter data. To end your input, enter a backslash and a period (\.).

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1,"iamtext","iamvarchar",2006-07-07,12:00:00
>> 2,"sometext","somevarchar",2006-07-07,12:00:00
>> \.

View the imported data.

select * from copy_example;
 col_1 |  col_2   |    col_3    |        col_4        |  col_5   
-------+----------+-------------+---------------------+----------
     1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
     2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
(2 rows)

Method 2: The example.csv file exists in the local /tmp/data/ directory. The file contains the header line. Use vertical bars (|) as the delimiters and quotation marks (") as the quotes. The content is as follows:

header
3|"sometext"|"somevarchar"|2006-07-07|12:00:00
4|"sometext"|"somevarchar"|2022-07-07|19:00:02

Import data from the local file example.csv to the target table copy_example. If the header option is on, the first row is automatically ignored. Use quotation marks (") as the quotes by default.

\copy copy_example from  '/tmp/data/example.csv' with(header 'on', format 'csv', delimiter '|', date_format 'yyyy-mm-dd',  time_format 'hh24:mi:ss');

View the imported data.

select * from copy_example;
 col_1 |  col_2   |    col_3    |        col_4        |  col_5   
-------+----------+-------------+---------------------+----------
     1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
     2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
     3 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
     4 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
(4 rows)

Method 3: The example2.csv file exists in the local directory /tmp/data/. Use commas (,) as the delimiters and quotation marks (") as the quotes. The last field is missing in the first line, and the last field is added in the second line. The content is as follows:

5,"sometext","somevarchar",2006-07-07
6,"sometext","somevarchar",2022-07-07,19:00:02,12:00:00

Import data from the local file example2.csv to the target table copy_example. The default delimiters are commas (,). Because the error tolerance parameters IGNORE_EXTRA_DATA and FILL_MISSING_FIELDS are specified, the missing fields are replaced with NULL, and the extra fields are ignored.

\copy copy_example from  '/tmp/data/example2.csv' with( format 'csv', date_format 'yyyy-mm-dd',  time_format 'hh24:mi:ss', IGNORE_EXTRA_DATA 'true', FILL_MISSING_FIELDS 'true');

View the imported data.

select * from copy_example;
 col_1 |  col_2   |    col_3    |        col_4        |  col_5   
-------+----------+-------------+---------------------+----------
     1 | iamtext  | iamvarchar  | 2006-07-07 00:00:00 | 12:00:00
     2 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
     3 | sometext | somevarchar | 2006-07-07 00:00:00 | 12:00:00
     4 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
     5 | sometext | somevarchar | 2006-07-07 00:00:00 | 
     6 | sometext | somevarchar | 2022-07-07 00:00:00 | 19:00:02
(6 rows)