Re: smart copy? - Mailing list pgsql-admin
From | Joel Burton |
---|---|
Subject | Re: smart copy? |
Date | |
Msg-id | JGEPJNMCKODMDHGOBKDNAEHCCLAA.joel@joelburton.com Whole thread Raw |
In response to | smart copy? (Lucas Rockwell <lr@socrates.Berkeley.EDU>) |
Responses |
Re: smart copy?
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: smart copy? ("Nigel J. Andrews" <nandrews@investsystems.co.uk>) |
List | pgsql-admin |
If you have a table in database A and want to move it to database B (on the same or different server): Drop from your table in Db A ("A.TheTable") all columns that should be automagically filled in on database B. This would be any nextval() columns, etc. [It's possible that there aren't present at all in the database A version, but if they are, get rid of them.] $ pg_dump --attribute-inserts --data-only --table=TheTable A Will dump the data from A.TheTable, but rather than as a COPY command, it will dump it as a series of INSERT commands, in the form: INSERT INTO TheTable (cola, colb, colc) VALUES (1, 'foo', 'bar'); So that, once you've created the table in database B , you can just pipe this through psql. It's slower than running COPY, since you're executing dozens/hundreds/kajillions of INSERT commands rather than one COPY command. If that's a problem, you can reduce it a bit by dropping indexes and triggers on B.TheTable, doing the INSERTs, then re-adding these. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Lucas Rockwell > Sent: Friday, April 26, 2002 3:59 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] smart copy? > > > hi all, > > is there a "smart" version of copy that will allow you to copy in data > when you have a column that is a default nextval(...) but you have no > data for that column? > > basically, i want to import data into a table and the primary > key value does not exist in my data to import. is this possible? > > the "Copy" section of Bruce's book doesn't cover this situation -- unless > i'm missing something. i skimmed the copy man page and didn't see any > mention there either. > > thanks. > > -lucas > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
pgsql-admin by date: