Copy table structure into new table
SqlPostgresqlSql Problem Overview
Is there a way to copy the structure of a table into a new table, without data, including all keys and constraints?
Sql Solutions
Solution 1 - Sql
For a simple schema copy use the like clause.
CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);
Solution 2 - Sql
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.
Solution 3 - Sql
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
Solution 4 - Sql
Take a look at http://www.pgadmin.org/">pgAdmin</a> - by far the easiest way to do what you want.
Right-click on table, Scripts - Create.
Solution 5 - Sql
How about
CREATE TABLE sample_table_copy AS (SELECT * FROM sample_table WHERE 1 = 2)
Solution 6 - Sql
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!