Re: smart copy?

"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