Skip to content

Commit 85188ab

Browse files
tglsfdctglsfdc
committed
Extend COPY to support COPY (SELECT ...) TO ...
Bernd Helmle
1 parent 0d50657 commit 85188ab

File tree

16 files changed

+843
-328
lines changed

16 files changed

+843
-328
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 24 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.74 2006/04/22 03:03:11 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.75 2006/08/30 23:34:20 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -33,7 +33,7 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
3333
[ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
3434
[ FORCE NOT NULL <replaceable class="parameter">column</replaceable> [, ...] ]
3535

36-
COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
36+
COPY { <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
3737
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
3838
[ [ WITH ]
3939
[ BINARY ]
@@ -57,7 +57,8 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
5757
files. <command>COPY TO</command> copies the contents of a table
5858
<emphasis>to</> a file, while <command>COPY FROM</command> copies
5959
data <emphasis>from</> a file to a table (appending the data to
60-
whatever is in the table already).
60+
whatever is in the table already). <command>COPY TO</command>
61+
can also copy the results of a <command>SELECT</> query.
6162
</para>
6263

6364
<para>
@@ -97,7 +98,17 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
9798
<listitem>
9899
<para>
99100
An optional list of columns to be copied. If no column list is
100-
specified, all columns will be used.
101+
specified, all columns of the table will be copied.
102+
</para>
103+
</listitem>
104+
</varlistentry>
105+
106+
<varlistentry>
107+
<term><replaceable class="parameter">query</replaceable></term>
108+
<listitem>
109+
<para>
110+
A <command>SELECT</> query whose results are to be copied.
111+
Note that parentheses are required around the query.
101112
</para>
102113
</listitem>
103114
</varlistentry>
@@ -148,7 +159,8 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
148159
<para>
149160
Specifies copying the OID for each row. (An error is raised if
150161
<literal>OIDS</literal> is specified for a table that does not
151-
have OIDs.)
162+
have OIDs, or in the case of copying a <replaceable
163+
class="parameter">query</replaceable>.)
152164
</para>
153165
</listitem>
154166
</varlistentry>
@@ -265,7 +277,7 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
265277
COPY <replaceable class="parameter">count</replaceable>
266278
</screen>
267279
The <replaceable class="parameter">count</replaceable> is the number
268-
of rows inserted into or copied from the table.
280+
of rows copied.
269281
</para>
270282
</refsect1>
271283

@@ -274,7 +286,8 @@ COPY <replaceable class="parameter">count</replaceable>
274286

275287
<para>
276288
<command>COPY</command> can only be used with plain tables, not
277-
with views.
289+
with views. However, you can write <literal>COPY (SELECT * FROM
290+
<replaceable class="parameter">viewname</replaceable>) TO ...</literal>.
278291
</para>
279292

280293
<para>
@@ -320,8 +333,8 @@ COPY <replaceable class="parameter">count</replaceable>
320333
server in the case of <command>COPY TO</command>, but for
321334
<command>COPY FROM</command> you do have the option of reading from
322335
a file specified by a relative path. The path will be interpreted
323-
relative to the working directory of the server process (somewhere below
324-
the data directory), not the client's working directory.
336+
relative to the working directory of the server process (normally
337+
the cluster's data directory), not the client's working directory.
325338
</para>
326339

327340
<para>
@@ -737,14 +750,9 @@ COPY country FROM '/usr1/proj/bray/sql/country_data';
737750
</para>
738751

739752
<para>
740-
To copy into a file just the countries whose names start with 'A'
741-
using a temporary table which is automatically deleted:
753+
To copy into a file just the countries whose names start with 'A':
742754
<programlisting>
743-
BEGIN;
744-
CREATE TEMP TABLE a_list_countries AS
745-
SELECT * FROM country WHERE country_name LIKE 'A%';
746-
COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';
747-
ROLLBACK;
755+
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
748756
</programlisting>
749757
</para>
750758

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.167 2006/08/29 22:25:04 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.168 2006/08/30 23:34:21 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -739,8 +739,7 @@ testdb=&gt;
739739
</varlistentry>
740740

741741
<varlistentry>
742-
<term><literal>\copy <replaceable class="parameter">table</replaceable>
743-
[ ( <replaceable class="parameter">column_list</replaceable> ) ]
742+
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
744743
{ <literal>from</literal> | <literal>to</literal> }
745744
{ <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
746745
[ with ]
@@ -779,9 +778,7 @@ testdb=&gt;
779778
</para>
780779

781780
<para>
782-
<literal>\copy <replaceable
783-
class="parameter">table</replaceable> from <replaceable
784-
class="parameter">stdin | stdout</replaceable></literal>
781+
<literal>\copy ... from stdin | to stdout</literal>
785782
reads/writes based on the command input and output respectively.
786783
All rows are read from the same source that issued the command,
787784
continuing until <literal>\.</literal> is read or the stream

0 commit comments

Comments
 (0)