MogDB
Ecological Tools
Doc Menu

COPY

Function

COPY copies data between tables and files.

COPY FROM copies data from a file to a table, and COPY TO copies data from a table to a file.

Precautions

  • To run the COPY FROM FILENAME or COPY TO FILENAME statement, you must have the SYSADMIN permission. By default, user SYSADMIN is not allowed to run the COPY FROM FILENAME or COPY TO FILENAME statement on database configuration files, key files, certificate files, and audit logs, preventing user SYSADMIN from viewing or modifying sensitive files without authorization. To grant the permission, you need to change the setting of enable_copy_server_files.
  • COPY applies only to tables but not views.
  • To insert data to a table, you must have the permission to insert data.
  • If a list of columns is specified, COPY copies only the data of the specified columns between the file and the table. If a table has any columns that are not in the column list, COPY FROM inserts default values for those columns.
  • If a data source file is specified, the server must be able to access the file. If STDIN is specified, data flows between the client and the server. When entering data, use the TAB key to separate the columns of the table and use a backslash and a period (.) in a new row to indicate the end of the input.
  • COPY FROM throws an error if any row in the data file contains more or fewer columns than expected.
  • The end of the data can be represented by a line that contains only a backslash and a period (.). If data is read from a file, the end flag is unnecessary. If data is copied between client applications, an end tag must be provided.
  • In COPY FROM, \N is an empty string. To enter the actual value \N, use \N.
  • COPY FROM does not support data preprocessing during data import, such as expression operation and default value filling. If you need to preprocess data during the import, you need to import the data to a temporary table and then run SQL statements to insert the data to the table through operations. However, this method causes I/O expansion and reduces the import performance.
  • When a data format error occurs during COPY FROM execution, the transaction is rolled back. However, the error information is insufficient, making it difficult to locate the error data from a large amount of raw data.
  • COPY FROM and COPY TO apply to low concurrency and local import and export of a small amount of data.

Syntax

  • Copy data from a file to a table.

    COPY table_name [ ( column_name [, ...] ) ]
        FROM { 'filename' | STDIN }
        [ [ USING ] DELIMITERS 'delimiters' ]
        [ WITHOUT ESCAPING ]
        [ LOG ERRORS ]
        [ REJECT LIMIT 'limit' ]
        [ WITH ( option [, ...] ) ]
        | copy_option
        | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ copy_option [  ...] ];

    img NOTE: In the syntax, FIXED, FORMATTER ({column_name(offset, length)} [, …]) and [ copy_option […] ] can be in any sequence, but FIXED and FORMATTER must be used together.

  • Copy data from a table to a file.

    COPY table_name [ ( column_name [, ...] ) ]
        TO { 'filename' | STDOUT }
        [ [ USING ] DELIMITERS 'delimiters' ]
        [ WITHOUT ESCAPING ]
        [ WITH ( option [, ...] ) ]
        | copy_option
        | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ copy_option [  ...] ];
    
    COPY query
        TO { 'filename' | STDOUT }
        [ WITHOUT ESCAPING ]
        [ WITH ( option [, ...] ) ]
        | copy_option
        | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ copy_option [  ...] ];

    img NOTE:

    1. The syntax constraints of COPY TO are as follows: (query) is incompatible with [USING] DELIMITER. If the data comes from a query result, COPY TO cannot specify [USING] DELIMITERS.
    2. Use spaces to separate copy_option following FIXED FORMATTTER.
    3. copy_option is the native parameter, while option is the parameter imported by a compatible foreign table.
    4. In the syntax, FIXED, FORMATTER ({column_name(offset, length)} [, …]) and [ copy_option […] ] can be in any sequence, but FIXED and FORMATTER must be used together

    The syntax of the optional parameter option is as follows:

    FORMAT 'format_name'
    | OIDS [ boolean ]
    | DELIMITER 'delimiter_character'
    | NULL 'null_string'
    | HEADER [ boolean ]
    | FILEHEADER 'header_file_string'
    | FREEZE [ boolean ]
    | QUOTE 'quote_character'
    | ESCAPE 'escape_character'
    | EOL 'newline_character'
    | NOESCAPING [ boolean ]
    | FORCE_QUOTE { ( column_name [, ...] ) | * }
    | FORCE_NOT_NULL ( column_name [, ...] )
    | ENCODING 'encoding_name'
    | IGNORE_EXTRA_DATA [ boolean ]
    | FILL_MISSING_FIELDS [ boolean ]
    | COMPATIBLE_ILLEGAL_CHARS [ boolean ]
    | DATE_FORMAT 'date_format_string'
    | TIME_FORMAT 'time_format_string'
    | TIMESTAMP_FORMAT 'timestamp_format_string'
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'

    The syntax of the optional parameter copy_option is as follows:

    OIDS
    | NULL 'null_string'
    | HEADER
    | FILEHEADER 'header_file_string'
    | FREEZE
    | FORCE_NOT_NULL column_name [, ...]
    | FORCE_QUOTE { column_name [, ...] | * }
    | BINARY
    | CSV
    | QUOTE [ AS ] 'quote_character'
    | ESCAPE [ AS ] 'escape_character'
    | EOL 'newline_character'
    | ENCODING 'encoding_name'
    | IGNORE_EXTRA_DATA
    | FILL_MISSING_FIELDS
    | COMPATIBLE_ILLEGAL_CHARS
    | DATE_FORMAT 'date_format_string'
    | TIME_FORMAT 'time_format_string'
    | TIMESTAMP_FORMAT 'timestamp_format_string'
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'

Parameter Description

  • query

    Specifies that the results are to be copied.

    Value range: a SELECT or VALUES command in parentheses

  • table_name

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

    Value range: an existing table name

  • column_name

    Specifies an optional list of columns to be copied.

    Value range: any columns. All columns will be copied if no column list is specified.

  • STDIN

    Specifies that input comes from the standard input.

  • STDOUT

    Specifies that output goes to the standard output.

  • FIXED

    Fixes column length. When the column length is fixed, DELIMITER, NULL, and CSV cannot be specified. When FIXED is specified, BINARY, CSV, and TEXT cannot be specified by copy_option.

    img NOTE: The definition of fixed length is as follows:

    1. The column length of each record is the same.
    2. Spaces are used for column padding. Columns of the numeric type are left-aligned and columns of the string type are right-aligned.
    3. No delimiters are used between columns.
  • [USING] DELIMITER 'delimiters'

    The string that separates columns within each row (line) of the file, and it cannot be larger than 10 bytes.

    Value range: The delimiter cannot include any of the following characters: .abcdefghijklmnopqrstuvwxyz0123456789

    The default value is a tab character in text format and a comma in CSV format.

  • WITHOUT ESCAPING

    Specifies, in text format, whether to escape the backslash () and its following characters.

    Value range: text only

  • LOG ERRORS

    If this parameter is specified, the error tolerance mechanism for data type errors in the COPY FROM statement is enabled. Row errors are recorded in the public.pgxc_copy_error_log table in the database for future reference.

    Value range: a value set while data is imported using COPY FROM.

    img NOTE: The restrictions of this error tolerance parameter are as follows:

    • This error tolerance mechanism captures only the data type errors (DATA_EXCEPTION) that occur during data parsing of COPY FROM on the primary node of the database.
    • Before enabling error tolerance for COPY FROM for the first time in a database, check whether the public.pgxc_copy_error_log table exists. If not, call the copy_error_log_create() function to create it. If it does, copy its data elsewhere, delete it, and call the copy_error_log_create() function to create the table. For details about columns in the public.pgxc_copy_error_log table, see Table 1.
    • While a COPY FROM statement with specified LOG ERRORS is being executed, if public.pgxc_copy_error_log does not exist or does not have the table definitions compliant with those predefined in copy_error_log_create(), an error will be reported. Ensure that the error table is created using the copy_error_log_create() function. Otherwise, COPY FROM statements with error tolerance may fail to be run.
    • If existing error tolerance parameters (for example, IGNORE_EXTRA_DATA) of the COPY statement are enabled, the error of the corresponding type will be processed as specified by the parameters and no error will be reported. Therefore, the error table does not contain such error data.
  • LOG ERRORS DATA

    The differences between LOG ERRORS DATA and LOG ERRORS are as follows:

    1. LOG ERRORS DATA fills the rawrecord field in the error tolerance table.
    2. Only users with the super permission can use the LOG ERRORS DATA parameter.

      img CAUTION: If error content is too complex, it may fail to be written to the error tolerance table by using LOG ERRORS DATA, causing a task failure.

  • REJECT LIMIT'limit'

    Used with the LOG ERROR parameter to set the upper limit of the tolerated errors in the COPY FROM statement. If the number of errors exceeds the limit, later errors will be reported based on the original mechanism.

    Value range: a positive integer (1 to INTMAX) or unlimited

    Default value: If LOG ERRORS is not specified, an error will be reported. If LOG ERRORS is specified, the default value is 0.

    img NOTE: In the error tolerance mechanism described in the description of LOG ERRORS, the count of REJECT LIMIT is calculated based on the number of data parsing errors on the primary node of the database where the COPY FROM statement is executed, not based on the number of all errors on the primary node.

  • FORMATTER

    Defines the place of each column in the data file in fixed length mode. Defines the place of each column in the data file in the column(*offset,length*) format.

    Value range:

    • The value of offset must be larger than 0. The unit is byte.
    • The value of length must be larger than 0. The unit is byte.

    The total length of all columns must be less than 1 GB.

    Replace columns that are not in the file with null.

  • OPTION { option_name ' value ' }

    Specifies all types of parameters of a compatible foreign table.

    • FORMAT

      Specifies the format of the source data file in the foreign table.

      Value range: CSV, TEXT, FIXED, and BINARY

      • The CSV file can process newline characters efficiently, but cannot process certain special characters well.
      • The TEXT file can process certain special characters efficiently, but cannot process newline characters well.
      • In FIXED files, the column length of each record is the same. Spaces are used for padding, and the excessive part will be truncated.
      • All data in the BINARY file is stored/read as binary format rather than as text. It is faster than the text and CSV formats, but a binary-format file is less portable.

      Default value: TEXT

    • DELIMITER

      Specifies the character that separates columns within each row (line) of the file.

      img 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 for the TEXT format data cannot contain lowercase letters, digits, or special characters (.).
      • 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

      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. If header is off, an exported file does not contain a header.

      Value range: true/on and false/off

      Default value: false

    • QUOTE

      Specifies a quoted character string for a CSV file.

      Default value: single quotation marks (“)

      img NOTE:

      • The value of QUOTE cannot be the same as that of DELIMITER or NULL.
      • The value of QUOTE must be a single-byte character.
      • Invisible characters are recommended, such as 0x07, 0x08, and 0x1b.
    • ESCAPE

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

      Default value: single quotation marks (“) If the value is the same as that of QUOTE, it will be replaced by \0.

    • EOL 'newline_character'

      Specifies the newline character style of the imported or exported data file.

      Value range: multi-character newline characters within 10 bytes Common newline characters include \r (0x0D), \n (0x0A), and \r\n(0x0D0A). Special newline characters include $ and #.

      img NOTE:

      • The EOL parameter supports only the TEXT format for data import and export and does not support the CSV or FIXED format for data import. For forward compatibility, the EOL parameter can be set to 0x0D or 0x0D0A for data export in the CSV or FIXED format.
      • The value of EOL cannot be the same as that of DELIMITER or NULL.
      • The EOL parameter value cannot contain the following characters: .abcdefghijklmnopqrstuvwxyz0123456789.
    • FORCE_QUOTE { ( column_name [, …] ) | * }

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

      Value range: an existing column name

    • FORCE_NOT_NULL ( column_name [, …] )

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

      Value range: an existing column name

    • ENCODING

      Specifies that the file is encoded in the encoding_name. If this option is omitted, the current encoding format is used by default.

    • IGNORE_EXTRA_DATA

      Specifies whether to ignore excessive columns when the number of data source files exceeds the number of foreign table columns. This parameter is used only during data import.

      Value range: true/on and false/off

      • true/on: If the number of columns in a data source file is greater than that defined by the foreign table, the extra columns at the end of a row are ignored.
      • false/off: If the number of columns in a data source file is greater than that defined by the foreign table, the following error message is reported:

        extra data after last expected column

      Default value: false

      img NOTICE: If a newline character at the end of a row is missing and the row and another row are integrated into one, data in another row is ignored after the parameter is set to true.

    • COMPATIBLE_ILLEGAL_CHARS

      Specifies whether to tolerate invalid characters during data import. The parameter is valid only for data import using COPY FROM.

      Value range: true/on and false/off

      • true/on: No error message is reported and data import is not interrupted when there are invalid characters. Invalid characters are converted into valid ones, and then imported to the database.
      • false/off: An error occurs when there are invalid characters, and the import stops.

      Default value: false/off

      img NOTE: The rules for converting invalid characters are as follows:

      1. \0 is converted to a space.
      2. Other invalid characters are converted to question marks.
      3. When compatible_illegal_chars is set to true/on, after invalid characters such as NULL, DELIMITER, QUOTE, and ESCAPE are converted to spaces or question marks, an error message stating “illegal chars conversion may confuse COPY escape 0x20” will be displayed to remind you of possible parameter confusion caused by the conversion.
    • FILL_MISSING_FIELDS

      Specifies how to handle the problem that the last column of a row in a source data file is lost during data import.

      Value range: true/on and false/off

      Default value: false/off

    • DATE_FORMAT

      Specifies the DATE format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid DATE value For details, see Date and Time Processing Functions and Operators.

      img NOTE: You can use the TIMESTAMP_FORMAT parameter to set the DATE format to TIMESTAMP for data import. For details, see TIMESTAMP_FORMAT below.

    • TIME_FORMAT

      Specifies the TIME format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid TIME value. Time zones are not supported. For details, see Date and Time Processing Functions and Operators.

    • TIMESTAMP_FORMAT

      Specifies the TIMESTAMP format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid TIMESTAMP value. Time zones cannot be used. For details, see Date and Time Processing Functions and Operators.

    • SMALLDATETIME_FORMAT

      Specifies the SMALLDATETIME format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid SMALLDATETIME value For details, see Date and Time Processing Functions and Operators.

  • COPY_OPTION { option_name ' value ' }

    Specifies all types of native parameters of COPY.

    • NULL null_string

      Specifies the string that represents a null value.

      img NOTICE: When using COPY FROM, any data item that matches this string will be stored as a null value, so make sure that you use the same string as you used with COPY TO.

      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 TEXT format is \N.
      • The default value for the CSV format is an empty string without quotation marks.
    • 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. If header is off, an exported file does not contain a header.

    • FILEHEADER

      Specifies a file that defines the content in the header for exported data. The file contains data description of each column.

      img NOTICE:

      • This parameter is available only when header is on or true.
      • fileheader specifies an absolute path.
      • The file can contain only one row of header information, and ends with a newline character. Excess rows will be discarded. (Header information cannot contain newline characters.)
      • The length of the file including the newline character cannot exceed 1 MB.
    • FREEZE

      Sets the COPY loaded data row as frozen, like these data have executed VACUUM FREEZE.

      This is a performance option of initial data loading. The data will be frozen only when the following three requirements are met:

      • The table being loaded has been created or truncated in the current subtransaction before copying.
      • There are no cursors open in the current transaction.
      • There are no original snapshots in the current transaction.

      img NOTE: When COPY is completed, all the other sessions will see the data immediately. However, this violates the general principle of MVCC visibility, and users should understand that this may cause potential risks.

    • FORCE NOT NULL column_name [, …]

      In CSV COPY FROM mode, the specified column is not null. If the column is null, its value is regarded as a string of 0 characters.

      Value range: an existing column name

    • FORCE QUOTE { column_name [, …] | * }

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

      Value range: an existing column name

    • 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. When BINARY is specified, CSV, FIXED, and TEXT cannot be specified through option or copy_option.

    • CSV

      Enables the CSV mode. When CSV is specified, BINARY, FIXED, and TEXT cannot be specified through option or copy_option.

    • QUOTE [AS] 'quote_character'

      Specifies a quoted character string for a CSV file.

      Default value: single quotation marks (“)

      img NOTE:

      • The value of QUOTE cannot be the same as that of DELIMITER or NULL.
      • The value of QUOTE must be a single-byte character.
      • Invisible characters are recommended, such as 0x07, 0x08, and 0x1b.
    • ESCAPE [AS] 'escape_character'

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

      The default value is single quotation marks (“). If the value is the same as that of QUOTE, it will be replaced by \0.

    • EOL 'newline_character'

      Specifies the newline character style of the imported or exported data file.

      Value range: multi-character newline characters within 10 bytes Common newline characters include \r (0x0D), \n (0x0A), and \r\n (0x0D0A). Special newline characters include $ and #.

      img NOTE:

      • The EOL parameter supports only the TEXT format for data import and export and does not support the CSV or FIXED format. For forward compatibility, the EOL parameter can be set to 0x0D or 0x0D0A for data export in the CSV or FIXED format.
      • The value of EOL cannot be the same as that of DELIMITER or NULL.
      • The EOL parameter value cannot contain the following characters: .abcdefghijklmnopqrstuvwxyz0123456789.
    • ENCODING 'encoding_name'

      Specifies the name of a file encoding format.

      Value range: a valid encoding format

      Default value: current encoding format

    • IGNORE_EXTRA_DATA

      If the number of columns in a data source file is greater than that defined by the foreign table, the extra columns at the end of a row are ignored. This parameter is used only during data import.

      If this parameter is not used and the number of columns in the data source file is greater than that defined in the foreign table, the following error information is displayed:

      extra data after last expected column
    • COMPATIBLE_ILLEGAL_CHARS

      Specifies that invalid characters are tolerated during data import. Invalid characters are converted and then imported to the database. No error is reported and the import is not interrupted. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      If this parameter is not used, an error is reported when invalid characters are encountered during the import, and the import is interrupted.

      img NOTE: The rules for converting invalid characters are as follows:

      1. \0 is converted to a space.
      2. Other invalid characters are converted to question marks.
      3. When compatible_illegal_chars is set to true/on, after invalid characters such as NULL, DELIMITER, QUOTE, and ESCAPE are converted to spaces or question marks, an error message stating “illegal chars conversion may confuse COPY escape 0x20” will be displayed to remind you of possible parameter confusion caused by the conversion.
    • FILL_MISSING_FIELDS

      Specifies how to handle the problem that the last column of a row in a source data file is lost during data import.

      Value range: true/on and false/off

      Default value: false/off

      img NOTICE: Do not specify this option. Currently, it does not enable error tolerance, but will make the parser ignore the said errors during data parsing on the primary node of the database. Such errors will not be recorded in the COPY error table (enabled using LOG ERRORS REJECT LIMIT) but will be reported later by the database node. Therefore, do not specify this option.

    • DATE_FORMAT 'date_format_string'

      Specifies the DATE format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid DATE value For details, see Date and Time Processing Functions and Operators.

      img NOTE: You can use the TIMESTAMP_FORMAT parameter to set the DATE format to TIMESTAMP for data import. For details, see TIMESTAMP_FORMAT below.

    • TIME_FORMAT 'time_format_string'

      Specifies the TIME format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid TIME value. Time zones are not supported. For details, see Date and Time Processing Functions and Operators.

    • TIMESTAMP_FORMAT 'timestamp_format_string'

      Specifies the TIMESTAMP format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid TIMESTAMP value. Time zones cannot be used. For details, see Date and Time Processing Functions and Operators.

    • SMALLDATETIME_FORMAT 'smalldatetime_format_string'

      Specifies the SMALLDATETIME format for data import. The BINARY format is not supported. When data of such format is imported, error “cannot specify bulkload compatibility options in BINARY mode” will occur. The parameter is valid only for data import using COPY FROM.

      Value range: a valid SMALLDATETIME value For details, see Date and Time Processing Functions and Operators.

    The following special backslash sequences are recognized by COPY FROM:

    • \b: Backslash (ASCII 8)
    • \f: Form feed (ASCII 12)
    • \n: Newline character (ASCII 10)
    • \r: Carriage return character (ASCII 13)
    • \t: Tab (ASCII 9)
    • \v: Vertical tab (ASCII 11)
    • \digits: Backslash followed by one to three octal digits specifies that the ASCII value is the character with that numeric code.
    • \xdigits: Backslash followed by an x and one or two hex digits specifies the character with that numeric code.

Examples

-- Copy data from the tpcds.ship_mode file to the /home/omm/ds_ship_mode.dat file:
mogdb=# COPY tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat';

-- Output tpcds.ship_mode to stdout.
mogdb=# COPY tpcds.ship_mode TO stdout;

-- Create the tpcds.ship_mode_t1 table.
mogdb=# CREATE TABLE tpcds.ship_mode_t1
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
)
WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE)
;

-- Copy data from stdin to the tpcds.ship_mode_t1 table.
mogdb=# COPY tpcds.ship_mode_t1 FROM stdin;

-- Copy data from the /home/omm/ds_ship_mode.dat file to the tpcds.ship_mode_t1 table.
mogdb=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat';

-- Copy data from the /home/omm/ds_ship_mode.dat file to the tpcds.ship_mode_t1 table, with the import format set to TEXT (format 'text'), the delimiter set to \t' (delimiter E'\t'), excessive columns ignored (ignore_extra_data 'true'), and characters not escaped (noescaping 'true').
mogdb=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' WITH(format 'text', delimiter E'\t', ignore_extra_data 'true', noescaping 'true');

-- Copy data from the /home/omm/ds_ship_mode.dat file to the tpcds.ship_mode_t1 table, with the import format set to FIXED, fixed-length format specified (FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20))), excessive columns ignored (ignore_extra_data), and headers included (header).
mogdb=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' FIXED FORMATTER(SM_SHIP_MODE_SK(0, 2), SM_SHIP_MODE_ID(2,16), SM_TYPE(18,30), SM_CODE(50,10), SM_CARRIER(61,20), SM_CONTRACT(82,20)) header ignore_extra_data;

-- Delete the tpcds.ship_mode_t1 table:
mogdb=# DROP TABLE tpcds.ship_mode_t1;