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.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou 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