0

Fastest way to do insert in a table A with same structure of a table B:

INSERT INTO A
SELECT * FROM B

I can't rename table because in table A there are indexes that when query is executed it's impossible to know without read catalog and create a dynamic script to modify table B to be equal to table A. Also, on table A there are on it some views than it's difficult to delete.

I can't use COPY command to make a COPY TO and next a COPY FROM because database user haven't permission on file and directory.

Is there a way to do a COPY A FROM (SELECT * FROM B) ?

PostgreSQL version 9.2.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Luigi Saggese
  • 5,299
  • 3
  • 43
  • 94
  • 1
    What's wrong with this `INSERT`? – Yang Jun 13 '13 at 18:26
  • http://www.postgresql.org/docs/current/static/populate.html – Denis de Bernardy Jun 13 '13 at 18:29
  • 1
    @Yang it take a lot of time for 2mil records (about 40 minutes) – Luigi Saggese Jun 13 '13 at 18:30
  • 1
    @LuigiSaggese: disable triggers, indexes, etc., and follow a few of the other steps detailed on the page I linked to. You won't get much faster than that if you're copying a table without `copy`. In fact, Yang's deleted answer (`create table Aexample as select * from Bexample`) was actually pretty good in this respect, since it'll give you a raw table without any of the stuff that might slow you down. – Denis de Bernardy Jun 13 '13 at 18:32
  • 1
    Thanks for your suggestion @Denis but as i have wrote it's impossible for me to know table Aexample structure and i can't disable trigger and index on it because it's a production table. Than INSERT SELECT it's fastest way? – Luigi Saggese Jun 13 '13 at 18:36
  • 1
    You mean the table exists and is in production already? Then yeah, insert select in a single transaction will be fastest. Note that within that transaction, if you're sure about the sanity of your data, you might be able to alter the table to temporarily disable and reenable a few things too: http://www.postgresql.org/docs/current/static/sql-altertable.html – Denis de Bernardy Jun 13 '13 at 18:39
  • Yes table exists and is in production. Thanks for all suggestions! – Luigi Saggese Jun 13 '13 at 18:43

1 Answers1

0

Your question states the correct answer:

INSERT INTO A
SELECT * FROM B;

That's all you need.

Now you can also do this if A doesn't exist yet:

CREATE TABLE A (LIKE B INCLUDING ALL);
INSERT INTO A SELECT * FROM B;
Chris Travers
  • 25,424
  • 6
  • 65
  • 182