Postgres Pro
Downloads
Home   >   mailing lists

Thread: \copy combine with SELECT

\copy combine with SELECT

From
"jia ding"
Date:
Hi all,

I tried:
select id, name  into table2   from table1;
 \copy table2 to filename.txt
in order to export 2 columns from table1 to a file.

But, I am thinking, if there is a command can combine these two command together?
Maybe, something like: \copy select id,name  from table  to filename.txt

?

Nina

Re: \copy combine with SELECT

From
Chris
Date:
jia ding wrote:
> Hi all,
>
> I tried:
> select id, name  into table2   from table1;
>  \copy table2 to filename.txt
> in order to export 2 columns from table1 to a file.
>
> But, I am thinking, if there is a command can combine these two command
> together?
> Maybe, something like: \copy select id,name  from table  to filename.txt

Close.

copy tablename field1, field2 to 'filename';

http://www.postgresql.org/docs/8.1/static/sql-copy.html

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: \copy combine with SELECT

From
"Qingqing Zhou"
Date:
""jia ding"" <dingjia@gmail.com> wrote

> I tried:
> select id, name  into table2   from table1;
>  \copy table2 to filename.txt
> in order to export 2 columns from table1 to a file.
>
> But, I am thinking, if there is a command can combine these two
> command together?

Notice that COPY command can be used like this:
COPY <relname> ['(' columnList ')'] FROM/TO [WITH options]

Regards,
Qingqing

Re: \copy combine with SELECT

From
"jia ding"
Date:
yes, of couse COPY

but,
=> copy test to 'test.txt';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

On 3/16/06, Chris <dmagick@gmail.com> wrote:
jia ding wrote:
> Hi all,
>
> I tried:
> select id, name  into table2   from table1;
>  \copy table2 to filename.txt
> in order to export 2 columns from table1 to a file.
>
> But, I am thinking, if there is a command can combine these two command
> together?
> Maybe, something like: \copy select id,name  from table  to filename.txt

Close.

copy tablename field1, field2 to 'filename';

http://www.postgresql.org/docs/8.1/static/sql-copy.html

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: \copy combine with SELECT

From
Chris
Date:
jia ding wrote:
> yes, of couse COPY
>
> but,
> => copy test to 'test.txt';
> ERROR:  must be superuser to COPY to or from a file
> HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command
> also works for anyone.

Straight from the documentation:

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY
FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file
accessible to the psql client. Thus, file accessibility and access
rights depend on the client rather than the server when \copy is used.

http://www.postgresql.org/docs/8.1/static/sql-copy.html


Doesn't look like \copy lets you specify which fields to include though.

alive=# \d a
        Table "public.a"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |


db=# \copy a to ./blah.sql (works)

db=# \copy a a to ./blah.sql
\copy: parse error at "a"


> On 3/16/06, *Chris* <dmagick@gmail.com <mailto:dmagick@gmail.com>> wrote:
>
>     jia ding wrote:
>      > Hi all,
>      >
>      > I tried:
>      > select id, name  into table2   from table1;
>      >  \copy table2 to filename.txt
>      > in order to export 2 columns from table1 to a file.
>      >
>      > But, I am thinking, if there is a command can combine these two
>     command
>      > together?
>      > Maybe, something like: \copy select id,name  from table  to
>     filename.txt
>
>     Close.
>
>     copy tablename field1, field2 to 'filename';
>
>     http://www.postgresql.org/docs/8.1/static/sql-copy.html
>
>     --
>     Postgresql & php tutorials
>     http://www.designmagick.com/
>
>


--
Postgresql & php tutorials
http://www.designmagick.com/

Re: \copy combine with SELECT

From
Martijn van Oosterhout
Date:
On Fri, Mar 17, 2006 at 10:45:16AM +1100, Chris wrote:
> Doesn't look like \copy lets you specify which fields to include though.

Ofcourse it does, you just need to use parenthesis. From the manpage:

\copy table [ ( column_list ) ] { from | to } filename | stdin | stdout
[ with ] [ oids ] [ delimiter [as]  'character' ] [ null [as]  'string' ]

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

Re: \copy combine with SELECT

From
"jia ding"
Date:
Thanks Martijn.

\d test
       Table "public.test"
 Column |  Type   | Modifiers
 id         | integer |
 score    | integer |

I tried \copy test (score) to test.txt. It works well.

But, suppose I want to select rows  with score > 20 (just an example).
Then,\copy table [ ( column_list ) ] { from | to } filename  doesn't
help much.


Have a nice weekend!

N



On 3/17/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Fri, Mar 17, 2006 at 10:45:16AM +1100, Chris wrote:
> > Doesn't look like \copy lets you specify which fields to include though.
>
> Ofcourse it does, you just need to use parenthesis. From the manpage:
>
> \copy table [ ( column_list ) ] { from | to } filename | stdin | stdout
> [ with ] [ oids ] [ delimiter [as]  'character' ] [ null [as]  'string' ]
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFEGt++IB7bNG8LQkwRAvRlAJ9b3KJX82JviBnhTmexPa6nJlP2iACcD6gl
> 5jLonNJA7sZpl1jxY+LQQ1E=
> =eepY
> -----END PGP SIGNATURE-----
>
>
>