8. SQL Statements : COPY : Column Formats for COPY : COPY Format Details
 
Share this page                  
COPY Format Details
This section describes specifying the format of fields in the data file. When specifying data file formats for COPY INTO, be aware of the following points:
• Data from numeric columns, when written to text fields in the data file, is right-justified and filled with blanks on the left.
• When a COPY INTO statement is issued in the Terminal Monitor, the –i and –f command line flags control the format used to convert floating-point table data into text-type file data. To avoid rounding of large floating point values, use the sql command -f flag to specify a floating point format that correctly accommodates the largest value to be copied. For information about the -i and -f flags, see the sql command description in the Command Reference Guide.
• The COPY INTO section often uses the phrase "the display length of the corresponding table column". This means the length of the table column when formatted as a character string. This will be a standard length based on the table column type, and is independent of the actual column value. For example: the display length of an INTEGER column is 13, the display length of a SMALLINT column is 6, and so on.
The following table explains the details for the various COPY list formats. Unless otherwise noted, all non-binary formats can be followed by an optional delim to specify a delimiter (see Delimiters in the Data File).
Format
How Stored (COPY INTO)
How Read (COPY FROM)
BOOLEAN
Written as a single byte Boolean value (0=FALSE, 1=TRUE). (A binary format)
Same format as COPY INTO
BYTE(0)
Same as BYTE(n) where n is the display length of the corresponding table column.
Read as variable-length binary data terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
BYTE(n) where n
is 1 to the maximum row size configured, not exceeding 32,000.
Written as a fixed-length byte string. Exactly n bytes are written, padded with zeros if necessary. If given, the delimiter is written after the value and padding.
Read as a fixed-length byte string; exactly n bytes are read. If a delimiter is specified, one additional character is read and discarded.
BYTE VARYING(0)
Same as BYTE VARYING(n) where n is the display length of the table column.
Read as a variable-length byte string, preceded by a 5‑character, right‑justified length specifier. If a delimiter is specified, additional input is discarded until the delimiter is found.
BYTE VARYING(n) where n is 1 to the maximum row size configured, not exceeding 32,000.
Written as a fixed-length byte string preceded by a 5-character, right-justified length specifier. If necessary, the field is padded with zeros to the specified length. If given, the delimiter is written after the value and padding.
Read as a fixed-length byte string, preceded by a 5-character, right-justified length specifier. If a delimiter is specified, one additional character is read and discarded.
C0
Same as Cn where n is the display length of the corresponding table column
Read as a variable-length string, terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
Any control characters or tabs in the input are converted to spaces. C0 format supports \. The \ is discarded, and the next character is taken literally as part of the value (even if it would normally be the delimiter). To read a \ character, use \\.
Cn
Written as a fixed-length string, padded with blanks if necessary. Any "non-printing" character (meaning a control character or tab) is converted to a space. If given, the delimiter is written after the value and padding.
Read as a fixed-length string. If a delimiter is specified, one additional character is read and discarded.
Any control characters or tabs in the input are converted to spaces. Fixed-length Cn format does not support \.
CHAR(0)
Same as CHAR(n) where n is the display length of the corresponding table column.
Read as a variable-length string terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
Unlike C format, CHAR does not support \. CHAR also does not convert control characters or tabs. File data is read as is.
CHAR(n)
where n is 1 to the maximum row size configured, not exceeding 32,000 (16,000 in a UTF8 instance).
Written as a fixed-length string, padded with blanks if necessary. If given, the delimiter is written after the value and padding.
Unlike C format, CHAR does not do any conversion of control characters or tabs. Table data is output as-is.
Read as a fixed-length string. If a delimiter is specified, one additional character is read and discarded.
Unlike C format, CHAR does not convert control characters or tabs. File data is read as is.
D0
Instead of placing a value in the file, COPY writes the specified delimiter. (Unlike the Dn format, D0 format does not write the column name.)
COPY INTO requires that a delimiter be specified; D0 with no delimiter is not allowed.
Dummy field. Characters are read and discarded until the specified delimiter is encountered. If a delimiter is not specified, the first comma, tab, or newline ends the value.
Any \ found in the input means that the next character is to be taken literally, and is not a delimiter.
Dn
Dummy column. Instead of placing a value in the file, COPY writes the name of the column n times. For example, if you specify x=D1, the column name, x, is written once; if you specify x=D3, COPY writes xxx (the column name, three times), and so on. You can specify a delimiter as a column name, for example, NL=D1.
Dummy field. N characters are read and discarded.
COPY FROM does not allow a delimiter specification with a fixed-length dummy field.
DATE
Written as a date. (A binary format)
Read as a date. (A binary format)
DECIMAL
Written as a decimal number. (A binary format.)
Read as a decimal number. (A binary format)
FLOAT
Written as double‑precision floating point. (A binary format)
Read as double‑precision floating point. (A binary format)
FLOAT4
Written as single‑precision floating point. (A binary format)
Read as single‑precision floating point. (A binary format)
INTEGER
Written as integer of 4‑byte length. (A binary format)
Read as integer of 4‑byte length. (A binary format)
INTEGER1
Written as integer of 1‑byte length. (A binary format)
Read as integer of 1‑byte length. (A binary format)
LONG BYTE(0)
Identical to LONG VARCHAR
Identical to LONG VARCHAR
LONG NVARCHAR(0)
Written in segments. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of bytes in UTF-8 encoding. After the last data segment, a final zero-length segment is written (that is, 0 followed by a space).
The maximum segment size for the LONG NVARCHAR segment is 32727 bytes.
The UTF-8 encoded LONG NVARCHAR data segments are similar to LONG VARCHAR data segments. See the description for LONG VARCHAR(0) for an example of the encoded data segment.
If a delimiter is specified, it is written immediately following the last segment.
Read under the same format as COPY INTO. If a delimiter is specified, one character is read and discarded after the data value is read.
LONG VARCHAR(0)
Written in segments. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of characters. After the last data segment, a final zero-length segment is written (that is, 0 followed by a space). If a delimiter is specified, it is written immediately following the last segment. The maximum segment length is 32767.
The following example shows two data segments, followed by the termination zero length segment. The first segment is 5 characters long, the second segment is 10 characters long, and the termination segment is 0 character long:
5 abcde10 abcdefghij 0 (with a space after the terminating 0 character)
(In this example, the data that is in the originating table column is abcdeabcdefghij)
Read under the same format as COPY INTO. If a delimiter is specified, one character is read and discarded after the data value is read.
MONEY
Written as a scaled floating point value (a money value). (A binary format)
Read as a scaled floating-point values (a money value). (A binary format)
NCHAR(0)
Written as a Unicode string in UTF-8 encoding, preceded by a 5-character, right-justified byte count. The exact length of the column value is written, without padding. If a delimiter is specified, it is written after the value.
Read as a Unicode string in UTF-8 encoding, preceded by a 5-character, right-justified length specifier. (The length is a byte count, not a character count). If a delimiter is specified, additional input is discarded until the delimiter is encountered.
NVARCHAR(0)
Same as NCHAR(0)
Same as NCHAR(0)
NCHAR(n)
Written as a fixed-length Unicode string in UCS-2 encoding. N is the length in characters, not bytes. The value is padded to the specified length with UCS-2 blanks, if necessary. If a delimiter is specified, it is written after the value and padding.
Read using the same format as COPY INTO. If a delimiter is specified, after the value is read, one additional character is read and discarded.
NVARCHAR(n)
Written as a 2-byte binary integer length specifier, followed by that many Unicode characters using UCS-2 encoding. The value is padded if necessary to the field length n; the padding content is undefined. If a delimiter is specified, it is written after the value and padding.
The byte length of the written value excluding delimiter is 2n+2; the length n is in characters, not bytes, and does not include the initial length specifier.
Read using the same format as COPY INTO. If a delimiter is specified, after the value is read, one additional character is read and discarded.
SMALLINT
Written as an integer of 2‑byte length. (A binary format.)
Read as integer of 2‑byte length. (A binary format)
TEXT(0)
Written as a variable length string. If a delimiter is specified, it is written after the value.
If the originating column is C, CHAR, or NCHAR, trailing blanks are trimmed. If the originating column is TEXT, VARCHAR, or NVARCHAR, the column value is output exactly as-is (no padding, no trimming). If the originating column is a non-character, writes the result of converting the value to a character string, as-is with no padding.
COPY INTO using TEXT(0) format is the way to get variable width output with no padding.
Read as variable-length character string terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value
TEXT(n)
Written as a fixed-length string. The value is padded with NULL bytes (zeros) if necessary. If specified, the delimiter is written after the value and padding.
Reads a fixed-length field n characters wide; however if one of those characters is a NULL byte, the value stored into the table is terminated at that NULL byte. If a delimiter is specified, one additional character is read and discarded.
VARCHAR(0)
Same as VARCHAR(n), where n is the display length of the corresponding table column.
Read as a variable-length string, preceded by a 5‑character, right-justified length specifier. If a delimiter is specified, additional input is discarded until the delimiter is found.
VARCHAR(n)
where n is 1 to the maximum row size configured, not exceeding 32,000 (16,000 in a UTF8 instance).
Written as a fixed-length string preceded by a 5-character, right‑justified length specifier. If necessary, the value is padded with null characters to the specified length.
Read as a fixed-length string, preceded by a 5-character, right-justified length specifier. If a delimiter is specified, one additional character is read and discarded.
Note:  The dummy format (dn) behaves differently for COPY FROM and COPY INTO. When a table is copied into a file, n specifies the number of times the column name is repeated. When copying from a file to a table, n specifies the number of bytes to skip.
For user-defined data types (UDTs), use CHAR or VARCHAR.