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.
