Thread: 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
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)
>
"Joel Burton" <joel@joelburton.com> writes:
> $ pg_dump --attribute-inserts --data-only --table=TheTable A
>
> It's slower than running COPY, since you're executing
> dozens/hundreds/kajillions of INSERT commands rather than one COPY command.
Yup, lots slower. A better idea (which also doesn't require redoing
the export) is:
1. Create a temp table that matches the columns present in your data
file.
2. COPY from data file into temp table.
3. INSERT INTO target_table (column list) SELECT * FROM temp_table;
You can do pretty much any transformation you need to in the
INSERT/SELECT, so this generalizes to a lot of related cases
where your data file doesn't quite match the data layout you want.
> 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.
For a big import that will still be a good idea --- drop the indexes
and recreate after you've imported. As for triggers, that depends on
whether you need whatever they'd do ...
regards, tom lane
Tom Lane wrote: > "Joel Burton" <joel@joelburton.com> writes: > > $ pg_dump --attribute-inserts --data-only --table=TheTable A > > > > It's slower than running COPY, since you're executing > > dozens/hundreds/kajillions of INSERT commands rather than one COPY command. > > Yup, lots slower. A better idea (which also doesn't require redoing > the export) is: > > 1. Create a temp table that matches the columns present in your data > file. > > 2. COPY from data file into temp table. > > 3. INSERT INTO target_table (column list) SELECT * FROM temp_table; > > You can do pretty much any transformation you need to in the > INSERT/SELECT, so this generalizes to a lot of related cases > where your data file doesn't quite match the data layout you want. It would be easier if we supported COPY out of views. You could create the view you want and COPY out that. Unfortunately, we don't support COPY of views. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, 26 Apr 2002, Joel Burton wrote: > > [edited some stuff out here] > > 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. Ah, yes, the old dropping indexes etc. for loading data. I realise this is just about standard practice on more than just postgres but does it really make a difference? I ask this because I have recently (and am doing so again) reloading a large table with foriegn key constraints and indexes. I drop the table and recreate it with out the foriegn keys, thus killing the FK triggers and indexes. [Well actually I keep the primary key there]. Loading the data is fine. Sure it takes a while but then it takes a while to write out to file in the first place. However, when it comes to recreating the constraints and indexes it takes ages. The indexes took hours if I recall correctly. Has anyone actually done tests to confirm that load+index+constraint time is less that just a plain load with everything still enabled? Is the way pg_dump disables some of these things more efficient some how? I am obviously being a tad lazy here,I could run tests myself, but I'm currently restoring this table again and I'm thinking someone must have done this in the past anyway. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants