Limiting records in pg_dump

On 31/07/2009 18:27, Arnold, Sandra wrote:

Is there a way to just dump records equal to a specific value using
pg_dump? In Oracle, you can export data and use a WHERE clause and only
pull a range of data. We currently use this in Oracle to load data sets
in our Development database. As we move to PostgreSQL we will be
wanting to do the same thing. If we cannot do it using the pg_dump
command, I will need to find a work around.

Not with pg_dump - it's all-or nothing. However, you could use the COPY
command:

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

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Arnold, Sandra wrote:

Is there a way to just dump records equal to a specific value using
pg_dump? In Oracle, you can export data and use a WHERE clause and
only pull a range of data. We currently use this in Oracle to load
data sets in our Development database. As we move to PostgreSQL we
will be wanting to do the same thing. If we cannot do it using the
pg_dump command, I will need to find a work around.

you could create an alternate schema in your DB with VIEW's to your
database table(s) that filter it according to your requirements, then
pg_dump -n schemaname ....

Thanks for that suggestion. Since I have a schema on the database, I could create the view on my schema or even a table with the same name with just the records that I want to copy to the other database. I had thought about the table but not necessary the view.

We are just in the process of looking at PostgreSQL as a solution to replace Oracle. Those high Maintenance and Support cost have made it necessary to find a replacement for Oracle.

-----Original Message-----
From: John R Pierce [mailto:pierce@hogranch.com]
Sent: Friday, July 31, 2009 1:59 PM
To: Arnold, Sandra; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Limiting records in pg_dump

Arnold, Sandra wrote:

Is there a way to just dump records equal to a specific value using
pg_dump? In Oracle, you can export data and use a WHERE clause and
only pull a range of data. We currently use this in Oracle to load
data sets in our Development database. As we move to PostgreSQL we
will be wanting to do the same thing. If we cannot do it using the
pg_dump command, I will need to find a work around.

you could create an alternate schema in your DB with VIEW's to your
database table(s) that filter it according to your requirements, then
pg_dump -n schemaname ....

----- "Sandra Arnold" <ArnoldS@osti.gov> wrote:

Thanks for that suggestion. Since I have a schema on the database, I
could create the view on my schema or even a table with the same name
with just the records that I want to copy to the other database. I
had thought about the table but not necessary the view.

We are just in the process of looking at PostgreSQL as a solution to
replace Oracle. Those high Maintenance and Support cost have made it
necessary to find a replacement for Oracle.

How about the COPY command:
http://www.postgresql.org/docs/8.4/interactive/sql-copy.html

Be sure and read to the bottom where it explains that this is a server command and needs to be run on the server and as the Postgres user.

An alternative is \copy run from psql, documentation here:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html

It runs locally and as the local user.

Adrian Klaver
aklaver@comcast.net