Using a gsql Meta-Command to Import Data

The gsql tool of DWS provides the \copy meta-command to import data.

\copy Command

For details on the \copy command, see the following NOTE.

NOTE: \copy meta-command

Syntax

\copy { table [ ( column_list ) ] |

( query ) } { from | to } { filename |

stdin | stdout | pstdin | pstdout }

[ with ] [ binary ] [ delimiter

[ as ] 'character' ] [ null [ as ] 'string' ]

[ csv [ header ] [ quote [ as ]

'character' ] [ escape [ as ] 'character' ]

[ force quote column_list | * ] [ force

not null column_list ] ]

Description

You can run this command to import or export data after logging in to a database on any gsql client. Different from the COPY statement in SQL, this command performs read/write operations on local files rather than files on database servers. The accessibility and permissions of the local files are restricted to local users.
NOTE:
\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. GDS or COPY is preferred for data import.

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.

  NOTE:

  • 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 of TEXT data cannot contain any of the following characters: \.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 the string that represents a null value.

  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:

  • 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 (")

  NOTE:

  • The value of quote cannot be the same as that of the delimiter or null parameter.
  • The value of quote 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: 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

Examples

1. Create a target table a.

   mogdb=# CREATE TABLE a(a int);

2. Import data.

   1. Copy data from stdin to table a.

      mogdb=# \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.

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

   2. Copy data from a local file to table a. The following assumes that the local file is /home/omm/2.csv.

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

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