From 85188ab8838bf19cdf12298e1b6c29e12f9b9a3c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 30 Aug 2006 23:34:22 +0000 Subject: Extend COPY to support COPY (SELECT ...) TO ... Bernd Helmle --- doc/src/sgml/ref/copy.sgml | 40 ++++++++++++++++++++++++---------------- doc/src/sgml/ref/psql-ref.sgml | 9 +++------ 2 files changed, 27 insertions(+), 22 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index c1d87e601c2..cb1eaa08048 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -33,7 +33,7 @@ COPY tablename [ ( escape' ] [ FORCE NOT NULL column [, ...] ] -COPY tablename [ ( column [, ...] ) ] +COPY { tablename [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] @@ -57,7 +57,8 @@ COPY tablename [ ( COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to - whatever is in the table already). + whatever is in the table already). COPY TO + can also copy the results of a SELECT query. @@ -97,7 +98,17 @@ COPY tablename [ ( 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. + + + + + + query + + + A SELECT query whose results are to be copied. + Note that parentheses are required around the query. @@ -148,7 +159,8 @@ COPY tablename [ ( Specifies copying the OID for each row. (An error is raised if OIDS is specified for a table that does not - have OIDs.) + have OIDs, or in the case of copying a query.) @@ -265,7 +277,7 @@ COPY tablename [ ( count The count is the number - of rows inserted into or copied from the table. + of rows copied. @@ -274,7 +286,8 @@ COPY count COPY can only be used with plain tables, not - with views. + with views. However, you can write COPY (SELECT * FROM + viewname) TO .... @@ -320,8 +333,8 @@ COPY count server in the case of COPY TO, but for COPY FROM 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 + the cluster's data directory), not the client's working directory. @@ -737,14 +750,9 @@ COPY country FROM '/usr1/proj/bray/sql/country_data'; - 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': -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'; diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index d6528d0bc10..acac4d3daf3 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -739,8 +739,7 @@ testdb=> - \copy table - [ ( column_list ) ] + \copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout } [ with ] @@ -779,9 +778,7 @@ testdb=> - \copy table from stdin | stdout + \copy ... from stdin | to stdout reads/writes based on the command input and output respectively. All rows are read from the same source that issued the command, continuing until \. is read or the stream -- cgit v1.2.3