CREATE TABLE – the fancy way with LIKE

One tiny little feature many users of PostgreSQL have often forgotten is the ability to create similar tables. It happens quite frequently that you want to create a table, which is just like some other one. To achieve that most people will do ...

CREATE TABLE x AS SELECT ... LIMIT 0;

This works nicely, but what if you got 20 indexes and 50 default values around? Clearly, it can be painful to add all that later on.
But there is a better way:

test=# CREATE TABLE t_test (id serial, name text, PRIMARY KEY (id) );
NOTICE: CREATE TABLE will create implicit sequence "t_test_id_seq" for serial column "t_test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_test_pkey" for table "t_test"
CREATE TABLE

We have created a simple table:

test=# d t_test
Table "public.t_test"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('t_test_id_seq'::regclass)
name | text |
Indexes:
"t_test_pkey" PRIMARY KEY, btree (id)

The LIKE keywords allows you to do a lot of fancy stuff . You can include constraints or just ignore them. You can include or just ignore defaults. The cool thing here is that you can include indexes: PostgreSQL will create synthetic index names for you – no need to create them all manually. This is how it works:

test=# CREATE TABLE t_new ( LIKE t_test INCLUDING CONSTRAINTS INCLUDING INDEXES EXCLUDING DEFAULTS);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_new_pkey" for table "t_new"
CREATE TABLE

The result will be an empty table which has been created according to our specifications:

test=# d t_new
Table "public.t_new"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
name | text |
Indexes:
"t_new_pkey" PRIMARY KEY, btree (id)

Using LIKE is especially useful if you want to clone tables which have dozens of indexes and constraints.