Copy a table (including indexes) in postgres

New PostgreSQL ( since 8.3 according to docs ) can use "INCLUDING INDEXES":

# select version();                                             version------------------------------------------------------------------------------------------------- PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)(1 row)

As you can see I'm testing on 8.3.

Now, let's create table:

# create table x1 (id serial primary key, x text unique);NOTICE:  CREATE TABLE will create implicit sequence "x1_id_seq" for serial column "x1.id"NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x1_pkey" for table "x1"NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x1_x_key" for table "x1"CREATE TABLE

And see how it looks:

# \d x1                         Table "public.x1" Column |  Type   |                    Modifiers--------+---------+------------------------------------------------- id     | integer | not null default nextval('x1_id_seq'::regclass) x      | text    |Indexes:    "x1_pkey" PRIMARY KEY, btree (id)    "x1_x_key" UNIQUE, btree (x)

Now we can copy the structure:

# create table x2 ( like x1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x2_pkey" for table "x2"NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x2_x_key" for table "x2"CREATE TABLE

And check the structure:

# \d x2                         Table "public.x2" Column |  Type   |                    Modifiers--------+---------+------------------------------------------------- id     | integer | not null default nextval('x1_id_seq'::regclass) x      | text    |Indexes:    "x2_pkey" PRIMARY KEY, btree (id)    "x2_x_key" UNIQUE, btree (x)

If you are using PostgreSQL pre-8.3, you can simply use pg_dump with option "-t" to specify 1 table, change table name in dump, and load it again:

=> pg_dump -t x2 | sed 's/x2/x3/g' | psqlSETSETSETSETSETSETSETSETCREATE TABLEALTER TABLEALTER TABLEALTER TABLE

And now the table is:

# \d x3                         Table "public.x3" Column |  Type   |                    Modifiers--------+---------+------------------------------------------------- id     | integer | not null default nextval('x1_id_seq'::regclass) x      | text    |Indexes:    "x3_pkey" PRIMARY KEY, btree (id)    "x3_x_key" UNIQUE, btree (x)

[CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name    [ (column_name [, ...] ) ]    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]    [ TABLESPACE tablespace ]    AS query][1]  

Here is an example

CREATE TABLE films_recent AS  SELECT * FROM films WHERE date_prod >= '2002-01-01';

The other way to create a new table from the first is to use

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);      INSERT INTO films_recent         SELECT *           FROM books          WHERE date_prod >= '2002-01-01';  

Note that Postgresql has a patch out to fix tablespace issues if the second method is used

There are many answers on the web, one of them can be found here.

I ended up doing something like this:

create table NEW ( like ORIGINAL including all);insert into NEW select * from ORIGINAL

This will copy the schema and the data including indexes, but not including triggers and constraints.Note that indexes are shared with original table so when adding new row to either table the counter will increment.