Copy table structure into new table

For a simple schema copy use the like clause.

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

Well, the closest you can get with SQL is:

create table new (
    like old
    including defaults
    including constraints
    including indexes
);

But it will not copy everything. The most important things that are missing are FOREIGN KEYs. Also - triggers are also not copied. Not sure about other things.

Another way is to dump the table structure, change it's name in dump, and load it again:

pg_dump -s -t old databases | sed 's/old/new/g' | psql

But beware, that such simplistic sed will also change old to new in other places (for example if you have in your table column named "is_scolded" it will become "is_scnewed").

The question really is rather: why do you need it - because for various purposes, I would use different techniques.

Copy table structure into new table

Related

Recent Posts