copy.hlp
上传用户:blenddy
上传日期:2007-01-07
资源大小:6495k
文件大小:6k
- .pgaw:Help.f.t insert end "COPY" {bold} " moves data between Postgres tables and standard Unix 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 specified from the viewpoint of the backend. If stdin or stdout are specified, data flows through the client frontend to the backend.
- " {} "Synopsis" {bold} "
- " {} "
- COPY [ BINARY ] table [ WITH OIDS ]
- FROM { 'filename' | stdin }
- [ USING DELIMITERS 'delimiter' ]
- COPY [ BINARY ] table [ WITH OIDS ]
- TO { 'filename' | stdout }
- [ USING DELIMITERS 'delimiter' ]
- " {code} "Inputs" {bold} "
- " {} "BINARY" {italic} "
- Changes the behavior of field formatting, forcing all data to be stored or read as binary objects rather than as text.
- " {} "table" {italic} "
- The name of an existing table.
- " {} "WITH OIDS" {italic} "
- Copies the internal unique object id (OID) for each row.
- " {} "filename" {italic} "
- The absolute Unix pathname of the input or output file.
- " {} "stdin" {italic} "
- Specifies that input comes from a pipe or terminal.
- " {} "stdout" {italic} "
- Specifies that output goes to a pipe or terminal.
- " {} "delimiter" {italic} "
- A character that delimits the input or output fields.
-
- " {} "Outputs" {bold} "
- " {} "COPY" {italic} "
- The copy completed successfully.
- " {} "ERROR: error message" {italic} "
- The copy failed for the reason stated in the error message.
- " {} "Usage" {bold} "
- 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
- ZW ZIMBABWE
- .
- " {} "File Formats" {bold} "
- " {} "Text Format" {italic} "
- 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 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.
- 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" {bold} " 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" {bold} " 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. NULL attributes are represented as "\N". A literal 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
- convert backslash characters ("\") to double-backslashes ("\\") to ensure that they are loaded properly.
- " {} "Binary Format" {italic} "
- In the case of " {} "COPY BINARY" {bold} ", the first four bytes in the file will be the number of instances in the file. If this number is zero, the
- " {} "COPY BINARY" {bold} " 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 table below.
- " {} "Notes" {bold} "
- The " {} "BINARY" {bold} " keyword will force all data to be stored/read as binary objects 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 delimiter character will be quoted.
- You must have select access on any table whose values are read by " {} "COPY" {bold} ", and either insert or update access to a table into which values are being inserted by
- " {} "COPY" {bold} ". The backend also needs appropriate Unix permissions for any file read or written by
- " {} "COPY" {bold} ".
- The keyword phrase " {} "USING DELIMITERS" {bold} " 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" {bold} " with the psql instruction \copy. "