Using a gsql Meta-Command to Import Data
Tags: opengauss, database
Published: 2023-03-14 07:11:33

The GSQL tool provides the **\copy** meta-command to import data.

**\copy Command**
For details on the **\copy** command, see Table 1.

Table 1 \copy meta-command

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nyphef0ker2sxml51qkk.png)

**Parameter Description**
**·** table

Specifies the name (possibly schema-qualified) of an existing table.

Value range: an existing table name

**·** column_list

Specifies an optional list of columns to be copied.

Value range: any field in the table. If no column list is specified, all columns of the table will be copied.

**·** query

Specifies that the results are to be copied.

Value range: a **SELECT** or **VALUES** command in parentheses

**·** filename

Specifies the absolute path of a file. To run the **COPY** command, the user must have the write permission for this path.

**·** stdin

Specifies that input comes from the standard input.

**·** stdout

Specifies that output goes to the standard output.

**·** pstdin

Specifies that input comes from the gsql client.

**·** pstout

Specifies that output goes to the gsql client.

**·** binary
Specifies that data is stored and read in binary mode instead of text mode. In binary mode, you cannot declare **DELIMITER**, NULL, or **CSV**. After **binary** is specified, CSV, FIXED, and TEXT cannot be specified through **option** or **copy_option**.

**·** delimiter [ as ] 'character'
Specifies the character that separates columns within each row (line) of the file.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/559v9aims7fiaosc70eh.png)

Value range: a multi-character delimiter within 10 bytes

Default value:

**·** A tab character in TEXT format
**·** A comma (,) in CSV format
**·** No delimiter in FIXED format

**·** null [ as ] 'string'

Specifies the string that represents a null value.

Value range:

**-** The null value cannot be \r or \n. The maximum length is 100 characters.
**-** A null value cannot be the same as the delimiter or quote value.

Default value:

**-** The default value for the CSV format is an empty string without quotation marks.
**-** The default value for the TEXT format is \N.

**·** header

Specifies whether a file contains a **header** with the names of each column in the file. header is available only for CSV and FIXED files.

When data is imported, if **header** is on, the first row of the data file will be identified as the header and ignored. If **header** is **off**, the first row will be identified as a data row.

When data is exported, if **header** is **on**, fileheader must be specified. **fileheader** specifies the content in the header. If **header** is **off**, an exported file does not contain a header.

Value range: **true/on** and **false/off**

Default value: false

**·** quote [ as ] 'character'

Specifies a quoted character string for a CSV file.

Default value: a double quotation mark (")

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rrkst6xpe86j819xe66q.png)

**·** escape [ as ] 'character'

Specifies escape characters when the CSV format is used. The value must be a single-byte character.

Default value: a double quotation mark (") If the value is the same as that of **quote**, it will be replaced by **\0**.

**·** force quote column_list | *

In **CSV COPY TO** mode, forces quotation marks to be used for all non-null values in each specified column. Null values are not quoted.

Value range: an existing column name

**·** force not null column_list

Assigns a value to a specified column in **CSV COPY FROM** mode.

Value range: an existing column name

**·** force null column_list

Assigns a null string to a specified column in **CSV COPY FROM** mode.

Value range: an existing column name

**Examples**
1. Create a target table **a**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u8pu2z4h1ksvb5jb5ybw.png)

2. Import data.

Copy data from **stdin** to table **a**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g79mujr0mezu26fsy7n0.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b8resbwmpcmy6jf9hzc3.png)

Query data imported to table **a**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0zuohk74w4tkkept0bi4.png)

Copy data from a local file to table **a**. Assume that the local file is **/home/omm/2.csv**.

2.1 Commas (,) are used as delimiters.
2.2 If the number of columns defined in a source data file is greater than that in a foreign table, extra columns will be ignored during import.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/12ivrqhlnwjnoh9mloi3.png)