From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | Francisco Reyes <lists(at)stringsutils(dot)com> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Command line export or copy utility? |
Date: | 2007-05-22 23:31:36 |
Message-ID: | 1179876696.4660.179.camel@snafu.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joris Dobbelsteen | 2007-05-22 23:35:56 | Re: Lock table, Select for update and Serialization error |
Previous Message | Brent Wood | 2007-05-22 23:29:05 | Re: Command line export or copy utility? |