1

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?

kapso
  • 11,703
  • 16
  • 58
  • 76
  • 1
    Why do you need this? What is the underlying problem that you want to solve? It sounds a little be as if you should rather re-think the way you create your tables and constraints. Do you have any structured way to do that? When using cleanly structured SQL scripts (which are stored in a version control system) you can re-create your tables in any database you want at any time. We have good experience using Liquibase for this. –  Oct 30 '14 at 18:18
  • possible duplicate of [Copy a table (including indexes) in postgres](http://stackoverflow.com/questions/198141/copy-a-table-including-indexes-in-postgres) – Sean Vieira Oct 30 '14 at 18:21

3 Answers3

5

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.

0

Copy only structure with

Select * into newtable from oldtable where 1=2
Jonny Axelsson
  • 46
  • 3
  • 2
    `select .. into ...` is an old deprecated syntax that is only there for backwards compatibility. It's better to use standard SQL: `create table newtable as select * from ...` –  Oct 30 '14 at 21:09
0

Use CREATE TABLE AS SELECT where SELECT returns nothing.

CREATE TABLE 
    new_table 
AS 
    SELECT 
        * 
    FROM 
        old_table 
    WHERE 
        FALSE=TRUE
sergzach
  • 6,578
  • 7
  • 46
  • 84