Limiting records in pg_dump

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.

Thanks,

Sandra

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(at)iol(dot)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(at)hogranch(dot)com]
Sent: Friday, July 31, 2009 1:59 PM
To: Arnold, Sandra; pgsql-general(at)postgresql(dot)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 ....