Filename Specification for COPY
Filename must be enclosed in single quotation marks; the file specification can include a directory/path name. For COPY INTO, if the file does not exist, COPY creates the file.
UNIX: For COPY INTO, if the file already exists, COPY overwrites it.
VMS: For COPY INTO, if the file already exists, COPY creates another version of the file.
Windows File Types for COPY
File type can be specified using the optional type parameter. Type must be either T for text, or B for binary.
The traditional Windows newline indicator is a CR-LF pair (carriage return / linefeed). The newline indicator on other operating systems (such as UNIX) is a single linefeed with no carriage return. Windows uses the file type to control translation between Windows and UNIX style newline indicators, as well as control-Z translation.
A file in binary type mode reads or writes the data exactly as is, with no translation. A file in text type mode translates a single LF to CR-LF when writing. When reading a file in text mode, CR-LF pairs are read as single LF's, and if a control-Z occurs in the data file, end-of-file is returned and Windows stops reading data from that file.
By default, Ingres uses text mode for COPY INTO and COPY FROM only if all of the listed field formats are character types (c, char, text, varchar, or dummy). Otherwise, binary mode is used.
The binary-copy forms (COPY () FROM or COPY () INTO) use binary mode.
Note: Unicode formats (nchar, nvarchar), long varchar format, and the byte formats cause binary mode to be used by default.
COPY FROM recognizes CR-LF as a newline (nl) delimiter even if the input file is read in binary type mode. (This is true on non-Windows systems too, so that data files that were created by Windows applications can be read.)
For situations where the default file type choice is inappropriate, the file type can be specified explicitly. For example, if COPY INTO is creating a file to be read on a UNIX system, a file type of B (Binary) is appropriate. The resulting file will contain UNIX-style newlines (single linefeeds) instead of Windows-style newlines.
VMS File Types for COPY
File type can be specified using the optional type parameter. Type must be one of the values listed in the following table.
If type is omitted, COPY determines the file type as follows:
• If all fields in the file are character types (char, text, varchar), and the final delimiter is nl, csv, or ssv, COPY creates a text file.
• If the file contains variable length records, its file type is variable. Variable length records occur if one or more fields are stored as varchar(0).
• If none of the preceding conditions apply, COPY creates a binary file.
If type is specified, the contents of the file must be in accordance with these rules. If it is not, COPY creates the data file according to the preceding rules.
Last modified date: 01/30/2023