Re: Command line export or copy utility? - Mailing list pgsql-general

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;'


The only wrinkle is what to do when you need the DDL for the table
itself (say, when you want to create the same table with a subset of the
rows). The way I do this is to pg_dump the schema (-s) in the custom
format (-Fc). Then, I generate a table of contents with pg_restore -l,
edit the TOC to include only the entries I want, and then rerun
pg_restore with -L.

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0