<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.74 2006/04/22 03:03:11 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.75 2006/08/30 23:34:20 tgl Exp $

PostgreSQL documentation

-->

[ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]

[ FORCE NOT NULL <replaceable class="parameter">column</replaceable> [, ...] ]

COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]

COPY{<replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }

TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }

[ [ WITH ]

[ BINARY ]

files. <command>COPY TO</command> copies the contents of a table

<emphasis>to</> a file, while <command>COPY FROM</command> copies

data <emphasis>from</> a file to a table (appending the data to

whatever is in the table already).

whatever is in the table already). <command>COPY TO</command>

can also copy the results of a <command>SELECT</> query.

</para>

<para>

<listitem>

<para>

An optional list of columns to be copied. If no column list is

specified, all columns will be used.

specified, all columns of the table will be copied.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">query</replaceable></term>

<listitem>

<para>

A <command>SELECT</> query whose results are to be copied.

Note that parentheses are required around the query.

</para>

</listitem>

</varlistentry>

<para>

Specifies copying the OID for each row. (An error is raised if

<literal>OIDS</literal> is specified for a table that does not

have OIDs.)

have OIDs, or in the case of copying a <replaceable

class="parameter">query</replaceable>.)

</para>

</listitem>

</varlistentry>

COPY <replaceable class="parameter">count</replaceable>

</screen>

The <replaceable class="parameter">count</replaceable> is the number

of rowsinserted into orcopied from the table.

of rows copied.

</para>

</refsect1>

<para>

<command>COPY</command> can only be used with plain tables, not

with views.

with views. However, you can write <literal>COPY (SELECT * FROM

<replaceable class="parameter">viewname</replaceable>) TO ...</literal>.

</para>

<para>

server in the case of <command>COPY TO</command>, but for

<command>COPY FROM</command> you do have the option of reading from

a file specified by a relative path. The path will be interpreted

relative to the working directory of the server process (somewhere below

the data directory), not the client's working directory.

relative to the working directory of the server process (normally

thecluster'sdata directory), not the client's working directory.

</para>

<para>

</para>

<para>

To copy into a file just the countries whose names start with 'A'

using a temporary table which is automatically deleted:

To copy into a file just the countries whose names start with 'A':

<programlisting>

BEGIN;

CREATE TEMP TABLE a_list_countries AS

SELECT * FROM country WHERE country_name LIKE 'A%';

COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';

ROLLBACK;

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

</programlisting>

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.167 2006/08/29 22:25:04 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.168 2006/08/30 23:34:21 tgl Exp $

PostgreSQL documentation

-->

</varlistentry>

<varlistentry>

<term><literal>\copy <replaceable class="parameter">table</replaceable>

[ ( <replaceable class="parameter">column_list</replaceable> ) ]

<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }

{ <literal>from</literal> | <literal>to</literal> }

{ <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }

[ with ]

</para>

<para>

<literal>\copy <replaceable

class="parameter">table</replaceable> from <replaceable

class="parameter">stdin | stdout</replaceable></literal>

<literal>\copy ... from stdin | to stdout</literal>

reads/writes based on the command input and output respectively.

All rows are read from the same source that issued the command,

continuing until <literal>\.</literal> is read or the stream