PostgreSQL: SELECT INTO - how to create indexes?

Question

Hi,

since SELECT INTO NEW_TABLE FROM QUERY creates NEW_TABLE the new table will not have any indices. Is there some way to utilise SELECT INTO with an existing table where I've created the desired indices? I am aware of INSERT INTO TABLE SELECT ... but I've encountered very bad performance compared to SELECT INTO.

Thanks

Answer 1

Not sure what performance issues do you talk about, but generally, if you're making copy of table, it's much better to create indexes after inserting data.

I.e. - you do:

create table new_table as select * from old_table;

Then just create indexes.

One option to simplify index creation is to use pg_dump and it's -s and -t options, with some "grep":

pg_dump -s -t old_table database_name | \
    grep -E '^CREATE.*INDEX' | \
    sed 's/old_table/new_table/g'