Good ideas, all. but, what about keeping things like check constraints, foreign keys, etc? Hmmm... maybe, if I dumped the entire DB schema, with no data, and then looped thru the tables, creating a temp table (as you describe) with a funky name (such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to rename the table in the output... (eg /TABLE\s+TABLEaBcDeFgH_U/TABLE customers/ Ugh. I was hoping there was a cleaner way... -Ben On Wednesday 04 January 2006 23:35, you wrote: > On Wed, 4 Jan 2006 21:00:25 -0800, Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx> wrote: > > Is there a way to put a limit on pg_dump, so that it doesn't dump ALL data, > > but that matching a particular query? > > > > Something like: > > > > pg_dump -da --attribute-inserts -t "customers" \ > > --matching-query="select * from customers where id=11"; > > > > I'd like to selectively dump information from a query, but using the output > > format from pg_dump so that it can be used to create a (partial) database. > > > > Can this sort of thing be done? > > Not directly with pg_dump. > > You could create a table (create table customers_1 as select * from > customers where id=11) and dump that but remember to change the > tablename in the dump file or after loading it. You dont get any > pk/fk/indexes on the table definition. > > You could also use copy to stdout/stdin. > > eg dump > psql -d dbname -c "create temp table dump as select * from customers > where id=11; copy dump to stdout;" >dumpfile > > eg restore > psql -d newdb -c "copy customers from stdin" <dumpfile > > You might need to play around with supplying username/password. > > klint. > > +---------------------------------------+-----------------+ > : Klint Gore : "Non rhyming : > : EMail : kg@xxxxxxxxxxxxxx : slang - the : > : Snail : A.B.R.I. : possibilities : > : Mail University of New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---------------------------------------+-----------------+ > -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
