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 |
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 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) |
MONEY | Written as a scaled floating point value (a money value). (A binary format) | Read as a scaled floating-point value (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 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. |