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:
Table of Contents
|
1 |
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.
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 |
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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply