Re: Command line export or copy utility?

Reece Hart writes:

> On Tue, 2007-05-22 at 18:07 -0400, Francisco Reyes wrote:
>> Does anyone know of any export or copy utility that runs on FreeBSD?
>> I basically need a program that will connect to one database, do a
>> select and copy the result to a second database.
>
> Two options:
> 1) if you want a whole table or schema, a pipe works nicely:
> eg$ pg_dump -t <table> | psql
>
> 2) As of 8.2, you can formulate COPY commands with subqueries. For
> example:
> eg$ psql -c 'COPY (SELECT origin_id,origin FROM origin
> WHERE is_public order by 1) TO STDOUT'
>
> eg$ psql -c 'COPY (SELECT x FROM a WHERE x%2=1) TO STDOUT' \
> | psql -c 'COPY a FROM STDIN;'

For the archives.
If using a version prior to 8.2 one can do from within psql:

select * into temporary table tmp_copy_table from <TABLE>
where <CONDITION>;
copy tmp_copy_table to '<FULLPATH>';

This is primarily when one is trying to copy a subset of data.
If doing the full table then, as Reece mentioned, pg_dump is the best route.