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.
- 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. You can use the COPY command with plain tables, but not with views.
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 are strings that the output function generates for each attribute’s 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.
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 uses backslash quotation characters for data fields that match the delimiter character. 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.
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.
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.
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 Performance Server SQL working directory.
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.
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
COPY [BINARY] table
FROM { 'file name' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ];
COPY table
TO { 'file name' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ];
Inputs
The COPY command takes the following inputs:
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
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
- 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 \.