"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
