|
Linux Classes
Share This With a Friend
|
copy command help
COPY - Copies data between files and tables
SYNOPSIS
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
INPUTS
BINARY Changes the behavior of field formatting, forcing
all data to be stored or read in binary format
rather than as text.
table The name of an existing table.
WITH OIDS
Copies the internal unique object id (OID) for each
row.
filename
The absolute Unix pathname of the input or output
file.
stdin Specifies that input comes from a pipe or terminal.
stdout Specifies that output goes to a pipe or terminal.
delimiter
A character that delimits the input or output
fields.
null print
A string to represent NULL values. The default is
``\N'' (backslash-N). You might prefer an empty
string, for example.
Note: On a copy in, any data item that matches this
string will be stored as a NULL value, so you
should make sure that you use the same string as
you used on copy out.
OUTPUTS
COPY The copy completed successfully.
The copy failed for the reason stated in the error
message.
DESCRIPTION
COPY moves data between Postgres tables and standard file-
system files. COPY instructs the Postgres backend to
directly read from or write to a file. The file must be
directly visible to the backend and the name must be spec
ified from the viewpoint of the backend. If stdin or std
out are specified, data flows through the client frontend
to the backend.
NOTES
The BINARY keyword will force all data to be stored/read
as binary format rather than as 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.
By default, a text copy uses a tab ("\t") character as a
delimiter. The delimiter may also be changed to any other
single character with the keyword phrase USING DELIMITERS.
Characters in data fields which happen to match the delim
iter character will be backslash quoted.
You must have select access on any table whose values are
read by COPY, and either insert or update access to a
table into which values are being inserted by COPY. The
backend also needs appropriate Unix permissions for any
file read or written by COPY.
The keyword phrase USING DELIMITERS specifies a single
character to be used for all delimiters between columns.
If multiple characters are specified in the delimiter
string, only the first character is used.
Tip: Do not confuse COPY with the psql instruction
\copy.
COPY neither invokes rules nor acts on column defaults.
It does invoke triggers, however.
COPY stops operation at the first error. This should not
lead to problems in the event of a COPY FROM, but the tar
get relation will, of course, be partially modified in a
COPY TO. VACUUM should be used to clean up after a failed
copy.
Because the Postgres backend's current working directory
path information) may yield unexpected results for the
naive user. In this case, foo will wind up in $PGDATA/foo.
In general, the full pathname as it would appear to the
backend server machine should be used when specifying
files to be copied.
Files used as arguments to COPY must reside on or be
accessible to the database server machine by being either
on local disks or on a networked file system.
When a TCP/IP connection from one machine to another is
used, and a target file is specified, the target file will
be written on the machine where the backend is running
rather than the user's machine.
FILE FORMATS
TEXT FORMAT
When COPY TO is used without the BINARY option, the file
generated will have each row (instance) on a single line,
with each column (attribute) separated by the delimiter
character. Embedded delimiter characters will be preceded
by a backslash character ("\"). The attribute values them
selves are strings generated by the output function asso
ciated 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.
The actual format for each instance is
<attr1><separator><attr2><separator>...<separator><attrn><newline>
The oid is placed on the beginning of the line if WITH
OIDS is specified.
If COPY is sending its output to standard output instead
of a file, it will send a backslash("\") and a period
(".") followed immediately by a newline, on a separate
line, when it is done. Similarly, if COPY is reading from
standard input, it will expect a backslash ("\") and a
period (".") followed by a newline, as the first three
characters on a line to denote end-of-file. However, COPY
will terminate (followed by the backend itself) if a true
EOF is encountered before this special end-of-file pattern
is found.
The backslash character has other special meanings. A lit
eral backslash character is represented as two consecutive
backslashes ("\\"). A literal tab character is represented
as a backslash and a tab. A literal newline character is
represented as a backslash and a newline. When loading
text data not generated by Postgres, you will need to con
BINARY FORMAT
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 read
ing when this number of instances has been read. Remain
ing 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
table below. "CONTENTS OF A BINARY COPY FILE" At the
start of the fileuint32number of tuplesFor each
tupleuint32total length of tuple datauint32oid (if speci
fied)uint32number of null
attributes[uint32,...,uint32]attribute numbers of
attributes, counting from 0-<tuple data>
ALIGNMENT OF BINARY DATA
On Sun-3s, 2-byte attributes are aligned on two-byte
boundaries, and all larger attributes are aligned on four-
byte boundaries. Character attributes are aligned on sin
gle-byte boundaries. On most other machines, all
attributes larger than 1 byte are aligned on four-byte
boundaries. Note that variable length attributes are pre
ceded by the attribute's length; arrays are simply con
tiguous streams of the array element type.
USAGE
The following example copies a table to standard output,
using a vertical bar ("|") as the field delimiter:
COPY country TO stdout USING DELIMITERS '|';
To copy data from a Unix file into a table "country":
COPY country FROM '/usr1/proj/bray/sql/country_data';
Here is a sample of data suitable for copying into a table
from stdin (so it has the termination sequence on the last
line):
AF AFGHANISTAN
AL ALBANIA
DZ ALGERIA
...
ZM ZAMBIA
The same data, output in binary format on a Linux/i586
machine. The data is shown after filtering through the
Unix utility od -c. The table has three fields; the first
is char(2) and the second is text. All the rows have a
null value in the third field. Notice how the char(2)
field is padded with nulls to four bytes and the text
field is preceded by its length:
355 \0 \0 \0 027 \0 \0 \0 001 \0 \0 \0 002 \0 \0 \0
006 \0 \0 \0 A F \0 \0 017 \0 \0 \0 A F G H
A N I S T A N 023 \0 \0 \0 001 \0 \0 \0 002
\0 \0 \0 006 \0 \0 \0 A L \0 \0 \v \0 \0 \0 A
L B A N I A 023 \0 \0 \0 001 \0 \0 \0 002 \0
\0 \0 006 \0 \0 \0 D Z \0 \0 \v \0 \0 \0 A L
G E R I A
... \n \0 \0 \0 Z A M B I A 024 \0
\0 \0 001 \0 \0 \0 002 \0 \0 \0 006 \0 \0 \0 Z W
\0 \0 \f \0 \0 \0 Z I M B A B W E
COMPATIBILITY
SQL92
There is no COPY statement in SQL92.
Comments - most recent first
|
I welcome your comments. However... I am puzzled by many people
who say "Please send me the Linux tutorial." This website *is* your Linux Tutorial! Read everything here, learn
all you can, ask questions if you like. But don't ask me to send what you already have. :-)
|
|
||
 |
Copyright © by Bob Rankin - Privacy Policy
All rights reserved - Redistribution is allowed only with permission.