Database Administrator Guide > Database Administrator Guide > Populating Tables > Formatted Copying > Column Name and Format Specifications
Was this helpful?
Column Name and Format Specifications
When using the COPY statement to do formatted copying, specify the column name and the format in which that column’s data is to be copied, as follows:
column_name = format [null-clause]
where:
column_name
Specifies the column from which data is read or to which data is written. The name in the copy target must be the same as in the copy source; you cannot change column names in the COPY statement.
format
Specifies the format of the column data in the data file. This format may be different from that column’s type or format in the table. For example, a table may contain an INTEGER column, but to copy the column into a text file, the COPY format should be a character-type format. The COPY statement can copy all data types except logical keys.
The column names and their formats must be separated by commas, and the list must be in parentheses.
Summary of Data Types and Storage Formats
A summary of data types and their storage format characteristics is given in the table below. For detailed information on storage formats and data conversions of the various data types, see the SQL Reference Guide.
Class
COPY Format
Description and Copy Notes
Character data
char
Fixed-length strings with blank padding at the end.
Character data
text
Variable-length strings with no padding.
Character data
varchar
Variable-length strings preceded by a length specifier.
Character data
long varchar
A segmented format for LONG VARCHAR or LONG BYTE data.
Unicode data
nchar
Fixed-length Unicode strings in UCS-2 encoding, or variable-length Unicode strings in UTF-8 encoding.
Unicode data
nvarchar
Fixed-length Unicode strings in UCS-2 encoding, or variable-length Unicode strings in UTF-8 encoding.
Unicode data
long nvarchar
File format for LONG NVARCHAR data.
Binary data
byte
Fixed or variable-length binary bytes.
Binary data
byte varying
Variable-length binary data preceded by a length.
Binary data
long byte
File format for LONG BYTE data.
Numeric data
integer1
Integer of 1-byte length (-128 to +127).
Numeric data
smallint
Integer of 2-byte length
(-32,768 to +32,767).
Numeric data
integer
Integer of 4-byte length
(-2,147,483,648 to +2,147,483,647).
Numeric data
decimal
Fixed-point exact numeric data, up to 31 digits. Range depends on precision and scale. Default is (5,0): -99999 to +99999.
Numeric data
float4
Single precision floating point number of 4-byte length (7 digit precision)
Numeric data
float
Double precision floating point number of 8-byte length (16 digit precision)
Date/time data
ansidate
4-byte binary
Date/time data
time
8- or 10-byte binary
Date/time data
timestamp
12- or 14-byte binary
Date/time data
interval year to month
3-byte binary
Date/time data
interval day to second
12-byte binary
Abstract data types
ingresdate
Date of 12-byte length, representing either absolute or relative date or date+time.
Abstract data types
money
Exact monetary data of 8-byte length,
$-999,999,999,999.99 to $999,999,999,999.99
Copy statement only
d
Dummy field.
• d0delim on COPY INTO copies the delimiter into the (empty) field.
• d0[delim] on COPY FROM skips the data in the field, up to the default or specified delimiter.
• dn on COPY INTO copies the name of the column n times. On COPY FROM, skips the field of n characters.
User-defined data types (UDTs)
 
Use char or varchar.
Last modified date: 01/30/2023