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.