Copy table structure into new table

user_94077 0
Viewed 113214

Is there a way to copy the structure of a table into a new table, without data, including all keys and constraints?

postgresql sql
6 Answers

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.

Admin1

To copy a table completely, the short form using the TABLE command can also be used:

CREATE TABLE films2 AS
    TABLE films
    WITH NO DATA;

More details here

user_18479380

Take a look at pgAdmin - by far the easiest way to do what you want.
Right-click on table, Scripts - Create.

user_1313680

I usually do this:

pg_dump dbname -s -t table_to_clone > /tmp/temp.sql

Than sed or vim of the file to change the table name and related stuff. Often is enough to replace table_to_clone with table_new_name.

At creation, I usually prefix with table name indexes and triggers, so at this point I have nothing more to do.

Now, from psql:

begin work;
\i /tmp/temp.sql

In this way, only if everything is fine I:

commit

and the game is done!

user_9982310
Related