This text was automatically converted from troff me macros to HTML. Information may have been lost, added, or changed in the process. Lars Aronsson and Lysator do not guarantee the correctness of this HTML document.
copy *- copy data to or from a class from or to a UNIX file.
copy [binary] classname direction ( "filename" | stdin | stdout )
Copy moves data between POSTGRES classes and standard UNIX files. The keyword binary change the behavior of field formatting, as described below. Classname is the name of an existing class. Direction is either to or from . Filename is the UNIX pathname of the file. In place of a filename, stdin and stdout can be used so that input to copy can be written by a LIBPQ application and output from the copy command can be read by a LIBPQ application. The binary keyword will force all data to be stored/read as binary objects rather than as ASCII text. It is somewhat faster than the normal copy command, but is not generally portable, and the files generated are somewhat larger, although this factor is highly dependent on the data itself.
When
copy
is used without the
binary
keyword, the file generated will have each instance on a line, with each
attribute separated by tabs (t). Embedded tabs will be preceeded by a
backslash character (\). The attribute values themselves are strings generated
by the output function associated with each attribute type. The output
function for a type should not try to generate the backslash character - this
will be handled by
copy
itself.
Note that on input to
copy
backslashes are considered to be special control characters, and should be
doubled if you want to embed a backslash, ie, the string "12\19\88" will be
converted by
copy
to "121988". The actual format for each instance is
<attr1><tab><attr2><tab>...<tab><attrn><newline>
If copy is sending its output to standard output instead of a file, it will send a period (.) followed immediately by a newline, on a line by themselves, when it is done. Similarly, if copy is reading from standard input, it will expect a period (.) followed by a newline, as the first two characters on a line, to denote end-of-file. However, copy will terminate (followed by the backend itself) if a true EOF is encountered.
NULL attributes are handled simply as null strings, that is, consecutive tabs in the input file denote a NULL attribute.
In the case of copy binary, the first four bytes in the file will be the number of instances in the file. If this number is zero, the copy binary command will read until end of file is encountered. Otherwise, it will stop reading when this number of instances has been read. Remaining data in the file will be ignored.
The format for each instance in the file is as follows. Note that this format must be followed EXACTLY. Unsigned four byte integer quantities are called uint32 in the below description.
uint32 totallength (not including itself),
uint32 number of null attributes
[uint32 attribute number of first null attribute
uint32 attribute number of nth null attribute],
<data>
"Alignment of binary data"
On Sun 3's, 2 byte attributes are aligned on two-byte boundaries, and all larger attributes are aligned on four-byte boundaries. Character attributes are aligned on single-byte boundaries. On other machines, all attributes larger than 1 byte are aligned on four-byte boundaries. Note that variable length attributes are preceeded by the attribute's length; arrays are simply contiguous streams of the array element type.
append(commands), create(commands), vacuum(commands), libpq.
Files used as arguments to the copy command must reside on or be accessable to the the database server machine by being either on local disks or a networked file system.
Copy stops operation at the first error. This should not lead to problems in the event of a copy from, but the target relation will, of course, be partially modified in a copy to. The ``vacuum'' query should be used to clean up after a failed copy.
Because POSTGRES operates out of a different directory than the user's working directory at the time POSTGRES is invoked, the result of copying to a file foo (without additional path information) may yield unexpected results for the naive user. The full pathname should be used when specifying files to be copied.
Copy has virtually no error checking, and a malformed input file will likely cause the backend to crash. Humans should avoid using copy for input whenever possible.