"transfering" indexes to other table

[Pet]

Hi,

since version 8.3 we can do following query:

create table newtable ( like oldtable INCLUDING DEFAULTS INCLUDING
CONSTRAINTS INCLUDING INDEXES );

I would prefer first create table without including index, because of
inserting big amount of data into newtable. And then I'd like
"transfer" all constraints and indexes and defaults to newtable.

Is it possible to do in SQL? May be create temp table where I insert
data without index and then select * insert into newtable?

Regards, Pet

[Laurenz Albe]

I don't think that there is a one-command way of doing that.

But there are things that can help you.

There is pg_get_indexdef(index_oid) that can generate a CREATE INDEX
statement for a certain index, and pg_get_constraintdef(constraint_oid)
to get the definition of a constraint.

You could write an (advanced) PL/pgSQL function that extracts all
constraint and index definitions from a source table and modifies
and applies them for a target table. It would mean digging around in
the system catalogs and using dynamic SQL, but it can be done.

So you could first create the table without constraints and indexes,
then fill the table and finally call the function to create the
constraints and indexes on the new table.

Yours,
Laurenz Albe

[Pet]

Hi Laurenz!

Thanks for your suggestions! Now I know where to start.

Pet

>
> Yours,
> Laurenz Albe