IBM PureData System for Analytics, Version 7.1

COPY

Restriction: Do not use the COPY command. For load or unload operations, use the nzload command or CREATE EXTERNAL TABLE commands. These commands are faster and more stable than the COPY command. The COPY command is documented here because it is used for internal operations.

The COPY command moves data between IBM® Netezza® tables and standard file system files. Use the COPY command with a file name to read directly from or write to a file.

Use the COPY TO command to copy the entire contents of a table to a file. The COPY TO command does not act on column defaults.
Use the COPY FROM command to copy data from a file to a table. If there is data in the table already, the command appends to the table.

There is no COPY command in SQL92.

By default, a text copy uses a tab ("\t") character as a delimiter between fields. You can change the field delimiter to any other single character with the keyword phrase by using delimiters. The system backslash quotation characters in data fields that happen to match the delimiter character.

You can use the COPY command with plain tables, but not with views.

The COPY command stops operation at the first error. For the COPY FROM command, this is not an issue. For the COPY TO command, the target relation already receives earlier rows. These rows are not visible or accessible, but they still occupy disk space. This might amount to a considerable amount of wasted disk space if the failure happens well into a large copy operation.

When you are using a file name, always specify an absolute path. The database server enforces an absolute path in the case of the COPY TO command, but for the COPY FROM command you have the option of reading from a file that is specified by a relative path. The system interprets the path relative to the working directory of the database server, not the Netezza SQL working directory.

When you use a COPY command, the system reads or writes a text file with one line per table row. The delimiter character separates columns (attributes) in a row. The attribute values that themselves are strings that the output function generates for each attribute data type, or that are acceptable to the input function. The system uses the specified null-value string in place of attributes that are null.

You can represent end-of-data by a single line that contains a backslash-period (\.). An end-of-data marker is not necessary when read from a UNIX file, but you must provide an end marker when you are copying data to or from a client application.

You can use backslash characters (\) in the COPY command data to quotation data characters that might otherwise be assumed to be row or column delimiters. Specifically, the following characters must be preceded by a backslash if they display as part of an attribute value: backslash itself, newline, and the current delimiter character.

The following table describes the COPY FROM command backslash sequences.

Table 1. COPY FROM Backslash Sequences
Sequence	Represents
\b	Backspace (ASCII 8)
\f	Form feed (ASCII 12)
\n	New line (ASCII 10)
\r	Carriage return (ASCII 13)
\t	Tab (ASCII 9)
\v	Vertical tab (ASCII 11)
\digits	Backslash followed by one to three octal digits specifies the character with that numeric code.

The COPY TO command does not output an octal-digits backslash sequence, but it does use the other sequences that are listed in the previous table for those control characters.

Restriction: Do not add a backslash before a data character N or period (.). Such pairs are mistaken for the default null string or the end-of-data marker. Any other backslash character that is not mentioned in the previous table is taken to represent itself.

Applications that generate COPY command data convert data newlines to \n sequences and convert carriage returns to \r sequences.

The end of each row is marked by a UNIX style newline ("\n"). The COPY FROM command does not work properly with a file that contains DOS-style or Mac-style newlines.

Syntax

Syntax for input that comes from a client application:

COPY [BINARY] table 
    FROM { 'file name' | stdin }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ];

Syntax for output that goes to a client application:

COPY table 
    TO { 'file name' | stdout }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ];

Inputs

The COPY command takes the following inputs:

Table 2. COPY Input
Input	Description
BINARY	Changes the behavior of field formatting. Forces all data to be stored or read in binary format rather than text.
delimiter	The character that separates fields within each row of the file.
file name	The absolute path name of the input or output file.
null string	The string that represents a NULL value. The default is "\N" (backslash-N). On a copy in, any data item that matches this string is stored as a NULL value. Ensure you use the same string as you used on copy out.
stdin	Input is to come from the client application.
stdout	Output is to go to the client application.
table	The name of an existing table.

Outputs

The COPY command has the following outputs.

Table 3. COPY outputs
Output	Description
`COPY`	The system returns this message if the copy completes successfully.
`ERROR: reason`	If a copy fails, the system returns this message with a reason for the failure.

Privileges

You must have Select privilege on any table whose values are read by the COPY command. You must have either Insert or Update privilege to a table into which values are being inserted by the COPY command. The database server must have the appropriate permissions for any file that is read or written by the COPY command.

Usage

The following provides sample usage.

Copy a table to standard output by using a vertical bar (|) as the field delimiter:
```
MYDB.SCH1(USER)=> COPY country TO stdout USING DELIMITERS '|';
```

Copy data from a UNIX file into the table country:

MYDB.SCH1(USER)=> COPY country FROM
'/usr1/proj/bray/sql/country_data';

This is a sample of data suitable for copying into a table from stdin (so it has the termination sequence on the last line):
```
AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE
\.
```