Re: smart copy?

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Lucas Rockwell
> Sent: Friday, April 26, 2002 3:59 PM
> To: pgsql-admin(at)postgresql(dot)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(at)postgresql(dot)org)
>