Thread: 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@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 tablewith the same name with just the records that I want to copy to the other database. I had thought about the tablebut not necessary the view. We are just in the process of looking at PostgreSQL as a solution to replace Oracle. Those high Maintenance and Supportcost 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 thePostgres 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