Re: smart copy?

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: smart copy?
Дата
Msg-id 12691.1019861442@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: smart copy?  ("Joel Burton" <joel@joelburton.com>)
Ответы Re: smart copy?  (Bruce Momjian <pgman@candle.pha.pa.us>)
Список pgsql-admin
"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

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Joel Burton"
Дата:
Сообщение: Re: smart copy?
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: smart copy?