CREATE TABLE – the fancy way

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 more simplistic 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, having dozens of indexes and constraints.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.postgresq-support.de), which has served countless customers around the globe.