CODEWITHSUNDEEP
sqlpostgresql
kapso
kapso

Reputation: 11903

Create a table from another table

I want to create a table from another table (schema copy) but not the data, just the schema. Is there a SQL or Postgres way of doing this? Hopefully something that can copy indexes & constraints as well.

Also once the table is created is it possible to keep the schema in sync, in case there are any future changes to the original table. Will save me manual sync of the schema. May be Postgres has something?

Upvotes: 1

Views: 1278

Answers (3)

sergzach
sergzach

Reputation: 6764

Use CREATE TABLE AS SELECT where SELECT returns nothing.

CREATE TABLE 
    new_table 
AS 
    SELECT 
        * 
    FROM 
        old_table 
    WHERE 
        FALSE=TRUE

Upvotes: 0

Jonny Axelsson
Jonny Axelsson

Reputation: 56

Copy only structure with

Select * into newtable from oldtable where 1=2

Upvotes: 0

user330315
user330315

Reputation:

Yes, use create table like

create table new_table (like old_table);

More details in the manual:
http://www.postgresql.org/docs/current/static/sql-createtable.html

The like clause will copy indexes and and check constraints, but not foreign key constraints.

It will not keep the new_table "in sync" with the old one though - and it's unclear to me why you would want that.

Upvotes: 5

Related Questions