169

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

Alex S
  • 25,241
  • 18
  • 52
  • 63
  • Select * Into NewTableName From OldTableName Where 1 = 2 https://stackoverflow.com/questions/2505728/create-table-structure-from-existing-table – Nityanand wachche Nov 29 '20 at 22:09

6 Answers6

218

For a simple schema copy use the like clause.

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);
Andreas Gebhard
  • 361
  • 2
  • 8
coderhs
  • 4,357
  • 1
  • 16
  • 25
  • 30
    Worth noting you can also add new columns when using the like syntax: `CREATE TABLE new (like old, extra_column text);` – Brad Koch Nov 02 '18 at 13:01
  • @BradKoch is it possible to add extra constraints within this statement? Or it should be a separate one? – Andrey Deineko May 31 '19 at 08:36
  • @AndreyDeineko It depends, check the [create table docs](https://www.postgresql.org/docs/current/sql-createtable.html) for full details. You can easily add check and foreign key constraints using this syntax, just like with any other create statement, but I'm not sure whether column level constraints like not null can be applied without a subsequent alter. – Brad Koch May 31 '19 at 13:13
  • 3
    Tried just now. Didn't copy foreign-key constraints and triggers (PostgreSQL 9.2). – Jānis Elmeris Sep 19 '19 at 11:08
  • 2
    Like the other answer, this works but has caveats. Besides the issue with FKs, this also causes the tables to share any sequences for serial cols. I'd just do this manually if possible. In most situations it's not hard to grab the CREATE TABLE, CREATE INDEX, etc commands for one table and change the names accordingly. – sudo Mar 24 '22 at 04:00
96

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.

  • Note: `including constraints` does not work on PostgreSQL 8.3 – Ragnar123 Jun 08 '11 at 13:48
  • 1
    Worked like a charm in Postgres 9.3 :) – Ganapathy Jul 01 '14 at 17:02
  • 19
    Best answer. Just beware that if you have a "serial" value or some other column defaulting to a sequence, it'll use the same sequence as the old table! So if you insert stuff into either table, it'll increment for both. – sudo Aug 05 '16 at 07:56
  • @sudo what's the solution for this? I want to create a new table but I also want to drop the old table which is throwing an error to me `PSQLException: ERROR: cannot drop table because other objects depend on it Detail: default value for column id of table depends on sequence` – Ashutosh Chamoli Mar 22 '22 at 11:58
  • @AshutoshChamoli Haven't tried this myself, but supposedly serials are implemented as default values + sequence, so you can do `ALTER TABLE yourtable ALTER COLUMN serialcol DROP DEFAULT;`. Then follow https://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres to add a new sequence if you want. – sudo Mar 24 '22 at 03:56
36

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

Hans Ginzel
  • 8,192
  • 3
  • 24
  • 22
Dimo Boyadzhiev
  • 1,178
  • 14
  • 17
12

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

ChssPly76
  • 99,456
  • 24
  • 206
  • 195
  • I only have access to phpPgAdmin; I don't own the server. – Alex S Aug 03 '09 at 02:32
  • Fair enough. In phpPgAdmin: navigate to the table, click Export, select "Structure Only" and you have your script – ChssPly76 Aug 03 '09 at 02:35
  • I'm pretty sure their must be a bug in this install - it just shows a blank page in the right frame when I do that :/ – Alex S Aug 03 '09 at 02:47
  • 1
    Did you try both "show" or "download" options? If both don't work then yes, it might be a bug. If so, you'll need to do it via SQL, take a look at link in Dav's answer. – ChssPly76 Aug 03 '09 at 02:52
  • Download just gives me an empty file. – Alex S Aug 03 '09 at 03:24
  • Most likely an installation error - perhaps phppgadmin is unable to access the pg_dump binary which it uses to get the structure. – Magnus Hagander Aug 03 '09 at 08:55
  • @Magnus - good call. There was a recent change to the server which required me to switch to absolute paths in exec(). I'll notify the appropriate people. – Alex S Aug 21 '09 at 02:57
10

How about

CREATE TABLE sample_table_copy AS (SELECT * FROM sample_table WHERE 1 = 2)

postgresql.org answer

picmate 涅
  • 3,951
  • 5
  • 43
  • 52
1

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!

Andrea Gelmini
  • 81
  • 1
  • 5