Using a gsql Meta-Command to Import Data

\copy Command

For the format and description of the \copy command, see Table 1.

Parameter Description

• table

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

  Value range: an existing table name

• column_list

  Indicates 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 will be copied.

  Value range: a SELECT or VALUES command in parentheses.

• filename

  Specifies the absolute path of a file. To run the \copy to command, you must have the write permission on the path. To run the \copy from command, you must have the read permission on the path.

• stdin

  Specifies that input comes from the client application.

• stdout

  Specifies that output goes to the client application.

• 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.

• oid

  Specifies the internal OID to be copied for each row.

  

  An error is raised if OIDs are specified for a table that does not have OIDs, or in the case of copying a query.

  Value range: true/on and false/off

  Default value: false

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

  

  • The value of delimiter cannot be \r or \n.
  • A delimiter cannot be the same as the null value. The delimiter for the CSV format cannot be same as the quote value.
  • The delimiter for the TEXT format data cannot contain backslashs (\), dots (.), lowercase letters, or digits, for example, \.abcdefghijklmnopqrstuvwxyz0123456789.
  • The data length of a single row should be less than 1 GB. A row that has many columns using long delimiters cannot contain much valid data.
  • You are advised to use multi-character delimiters or invisible delimiters. For example, you can use multi-characters (such as $^&) and invisible characters (such as 0x07, 0x08, and 0x1b).

  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 that a string represents a null value in a data file.

  Value range:

  • A 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:

  • an empty string without quotation marks in CSV format
  • \N in text format
• useeof

  Does not report an error for \. in the imported data.

  Value range: true or false

  Default value: false

• 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 quote character for a CSV file.

  Default value: ""

  

  • The quote value cannot be the same as the delimiter or null value.
  • The quote value must be a single-byte character.
  • Invisible characters are recommended, such as 0x07, 0x08, and 0x1b.
• escape [ as ] 'character'

  Specifies an escape character for a CSV file. The value must be a single-byte character.

  Default value: "" If the value is the same as the quote value, it will be replaced with \0.

• force quote column_list | *

  In CSV COPY TO mode, forces quoting to be used for all not-null values in each specified column. NULL values are not quoted.

  Value range: an existing column

• force not null column_list

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

  Value range: an existing column

Examples

1. Create a target table a.

   1	openGauss=# CREATE TABLE a(a int);

2. Import data.
   1. Copy data from stdin to table a.

      openGauss=# \copy a from stdin;

      When the >> characters are 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
      >> 2
      >> \.

      Query data imported to table a.

      1 2 3 4 5 6

      openGauss=# SELECT * FROM a; a --- 1 2 (2 rows)

   2. Copy data from a local file to table a. For example, the local file /home/omm/2.csv exists.
      • Commas (,) are used as delimiters.
      • 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.

      1	openGauss=# \copy a FROM '/home/omm/2.csv' WITH (delimiter',',IGNORE_EXTRA_DATA 'on');