Copy Data into a Formatted File
Use the following COPY statement to copy table data into a formatted file:
COPY [TABLE] [schema.]tablename
([column_name = format [WITH NULL [(value)]]
{, column_name = format [WITH NULL[(value)]]}])
INTO 'output_filename' [standard-with-clauses]
One or more column names appear, with format specifications. The column names must be the same as those in the table. However, the order of the columns can be different from the order in which they are currently stored in the table (except as noted below). Also, the format does not have to be the same data type or length as their corresponding entries in the table. The data is copied with any column reorganization or format conversions being made as necessary.
Note: When copying from a table that includes long varchar or long byte columns, you must specify the columns in the order they appear in the table.
Two major categories of data that can be unloaded into files are fixed-length fields and variable-length fields.
Data with Fixed-Length Fields
Fixed-length fields can use implicit or explicit specification of the field length.
• For COPY INTO, if you use the (0) notation for character or byte data, a fixed length is implicitly specified. For example, if you use char(0), character columns are copied into the file using the full display length of the column. The one exception to this rule is text(0), which exports variable width data with no padding.
• Columns containing numeric data (such as integer or float data types) can be explicitly formatted using the -i or -f SQL option flags. For details on these flags, see the sql command description in the Command Reference Guide.
• If you use a length specifier, the field length is explicit. For example, for char(n), the COPY INTO statement stores exactly n characters. Excess characters are discarded and shorter columns are padded with blanks.
• Some forms, such as varchar(n), stores exactly n characters with a leading length indicator in ASCII format. The length indicator is the exact length of the table value without padding. After the length and value, the field is padded out to the field width n. Thus, the field width in the file is fixed; but the data can be reloaded as variable width data, since each value carries its own length indicator. These formats are called "counted" formats; see the COPY details in the SQL Reference Guide.
Data with Variable-Length Fields
Variable-length data items are written to a file by the COPY statement with the formats:
text(0)
varchar(0)
long varchar(0)
byte varying(0)
long byte(0)
nvarchar(0)
long nvarchar(0)
An ASCII length is written preceding the data (except for text(0), which does not use a length specifier). The length of the data copied corresponds to the number of characters or bytes in the column, not the width of the column specified in the CREATE statement.
Last modified date: 01/30/2023