Column Formats

The following sections describe how to specify the data file format for table columns. The format specifies how each is stored and delimited in the data file.

Note: When copying to or from a table that includes long varchar or long byte columns, specify the columns in the order they appear in the table.

Storage Format

This section describes specifying the format of fields in the data file. When specifying storage 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 copying data from a floating-point table column to a text field in a data file, the format the data according to the options specified by the -i and -f flags. For information about these flags, see the sql command description in the System Administrator Guide.
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 this flag, see the sql command description in the System Administrator Guide.

The following table explains the data file formats for the various SQL data types. Delimiters are described in the section following this table.

Format	How Stored (Copy Into)	How Read (Copy From)
Byte(0)	Stored as fixed-length binary data (padded with zeros to the declared length if necessary).	Read as variable-length binary data terminated by the first comma, tab, or newline encountered.
Byte(0)delim	Stored as fixed-length binary data (padded with zeros to the declared length if necessary). The one-character delimiter is inserted immediately after the value. Because this format uses zeros to pad data, a zero is not a valid delimiter for this format.	Read as variable-length binary data terminated by the specified character.
Byte(n) where n is 1 to the maximum row size configured, not exceeding 32,000.	Stored as fixed-length binary data.	Read as fixed-length binary data.
Byte varying(0)	Stored as variable-length binary data preceded by a 5character, rightjustified length specifier.	Read as variable-length binary data, preceded by a 5character, rightjustified length specifier.
byte varying(n) (where n is 1 to the maximum row size configured, not exceeding 32,000.).	Stored as fixed-length binary data preceded by a 5-character, right-justified length specifier. If necessary, the value is padded with zeros to the specified length.	Read as fixed-length binary data, preceded by a 5-character, right-justified length specifier.
Char(0)	Stored as fixed-length strings (padded with blanks if necessary). For character data, the length of the string written to the file is the same as the column length.	Read as variable-length character string terminated by the first comma, tab, or newline encountered.
Char(0)delim	Stored padded to the declared width of the column. The one-character delimiter is inserted immediately after the value. Because this format uses spaces to pad data, a space (sp) is not a valid delimiter for this format.	Read as variable-length character string terminated by the specified character.
char(n) where n is 1 to the maximum row size configured, not exceeding 32,000.	Stored as fixed-length strings.	Read as fixed-length string.
D0	(not applicable)	Dummy field. Read as a variable-length character string terminated by the first comma, tab, or newline encountered. The data in the field is skipped.
D0delim	Indicates a delimited dummy column. Instead of placing a value in the file, copy inserts the specified delim. (Unlike the dn format, this format does not insert the column name.)	Dummy field. Read as a variable-length character string delimited by the specified character. The data in the field is skipped.
Date	Stored in date format.	Read as a date field.
decimal	Stored in decimal data format.	Read as decimal data.
Dn	Dummy column. Instead of placing a value in the file, copy inserts the name of the column n times. For example, if you specify x=d1, the column name, x, is inserted once; if you specify x=d2, copy inserts the column name, x, twice, and so on. You can specify a delimiter as a column name, for example, nl=d1.	Dummy field, read as a variable-length character string of the specified length. The data in the field is skipped.
Float	Stored as doubleprecision floating point.	Read as doubleprecision floating point.
Float4	Stored as singleprecision floating point.	Read as singleprecision floating point.
integer	Stored as integer of 4byte length.	Read as integer of 4byte length.
integer1	Stored as integer of 1byte length.	Read as integer of 1byte length.
Long byte(0)	Binary data stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of characters. The end of the column data is specified through a termination, zero length segment (that is, an integer 0 followed by a space). 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. The maximum length of each segment is 32737. 5 abcde10 abcdefghij 0 (with a space after the terminating 0 character) (In this example, the effective data that was in the column is abcdeabcdefghij) If the long byte column is nullable, specify the with null clause. An empty column is stored as an integer 0, followed by a space.	Read under the same format as copy into.
Long nvarchar(0)	Stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of Unicode characters in UTF-8 format. The end of the column data is specified through a termination, zero length segment (that is, an integer 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 the long nvarchar column is nullable, specify the with null clause. An empty column is stored as an integer 0, followed by a space.	Read under the same format as copy into.
Long varchar(0)	Stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of characters. The end of the column data is specified through a termination, zero length segment (that is, an integer 0 followed by a space). 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. The maximum length of each segment is 32737. 5 abcde10 abcdefghij 0 (with a space after the terminating 0 character) (In this example, the effective data that was in the column is abcdeabcdefghij) If the long varchar column is nullable, specify the with null clause. An empty column is stored as an integer 0, followed by a space.	Read under the same format as copy into.
money	Stored in money format.	Read as a money field.
nchar(0)	Stored as fixed-length Unicode strings in UTF-8 format (padded with blanks if necessary).	Read as fixed-length Unicode string in UTF-8 format but converted to UTF-16 for storage. The string is terminated by the first comma, tab, or newline encountered.
nvarchar(0)	Stored as a variable-length Unicode string in UTF-8 format preceded by a 5-character, right-justified length specifier.	Read as variable-length Unicode string in UTF-8 format, preceded by a 5-character, right-justified length specifier.
smallint	Stored as integer of 2byte length.	Read as integer of 2byte length.
varchar(0)	Stored as a variable-length string preceded by a 5-character, rightjustified length specifier.	Read as variable-length string, preceded by a 5character, right-justified length specifier.
varchar(n) (where n is 1 to the maximum row size configured, not exceeding 32,000.)	Stored as fixed-length strings preceded by a 5-character, rightjustified length specifier. If necessary, the value is padded with null characters to the specified length.	Read as fixed-length string, preceded by a 5-character, right-justified length specifier.

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.

Delimiters

Delimiters are those characters in the data file that separate fields and mark the end of records. Valid delimiters are listed in the following table:

Delimiter	Description
Nl	newline character
Tab	tab character
Sp	Space
nul or null	null character
comma	Comma
colon	Colon
Dash	Dash
lparen	left parenthesis
rparen	right parenthesis
X	any non-numeric character

When a single character is specified as the delimiter, enclose that character in quotes. If the data type specification is d0, the quotes must enclose the entire format. For example, 'd0%' specifies a dummy column delimited by a percent sign (%).

If the data type specification is char(0) or varchar(0), only the delimiter character must be quoted. For example, char(0)'%' specifies a char field delimited by a percent sign.

Do not use the space delimiter (sp) with char(0) fields: the char(0) format uses spaces as padding for character and numeric columns.

When copying from a table into a file, insert delimiters independently of columns. For example, to insert a newline character at the end of a line, specify 'nl=d1' at the end of the column list. This directs the DBMS Server to add one (d1) newline (nl) character. (Do not confuse lowercase 'l' with the number '1'.)

With Null Clause

When copying data from a table to a file, the with null clause directs copy to put the specified value in the file when a null value is detected in the corresponding column. Specify the with null clause for any column that is nullable. If the with null clause is omitted, the DBMS Server returns an error when it encounters null data, and aborts the copy statement.

When copying data from a file to a table, the with null clause specifies the value in the file to be interpreted as a null. When copy encounters this value in the file, it writes a null to the corresponding table column. The table column must be nullable.

To prevent conflicts between valid data and null entries, choose a value that does not occur as part of the data in your table. The value chosen to represent nulls must be compatible with the format of the field in the file: character formats require quoted values and numeric formats require unquoted numeric values. For example:

This example of a value is incorrect:

c0comma with null(0)

because the value specified for nulls (numeric zero) conflicts with the character data type of the field. However, this example is correct:

c0comma with null('0')

because the null value is character data, specified in quotes, and does not conflict with the data type of the field. Do not use the keyword null, quoted or unquoted, for a numeric format.

When copying from a table to a file, be sure that the specified field format is at least as large as the value specified for the with null clause. If the column format is too small, the DBMS Server truncates the null value written to the data file to fit the specified format.

For example, in the following statement the string, 'NULL,' is truncated to 'N' because the format is incorrectly specified as one character:

copy table t1 (col1 = varchar(1) with null ('NULL')) into 't1.dat';

The correct version specifies a 4-character format for the column.

copy table t1 (col1 = varchar(4) with null ('NULL')) into 't1.dat';

If with null is specified but value is omitted, copy appends a trailing byte indicating whether the field is null. For null fields, copy inserts an undefined data value in place of the null and sets the trailing byte to indicate a null field. Value must be specified for nullable char(0) and varchar(0) columns.