修改表結構
更新時間 2025-02-14 10:25:01
最近更新時間: 2025-02-14 10:25:01
分享文章
您可以根據實際情況修改表，例如修改表名、給表或字段添加注釋、給表增加字段、修改字段類型、修改字段默認值、刪除字段和管理主鍵等。
修改表名
teledb=# alter table t1 rename to teledb_t1;
ALTER TABLE給表或字段添加注釋
teledb=# comment on table teledb_t1 is '這是一條備注';
COMMENT
teledb=# \dt+ teledb_t1
List of relations
Schema | Name | Type | Owner | Size | Allocated Size | Description
--------+------------+-------+---------+-------+----------------+--------------
public | teledb_t1 | table | teledb | 16 kB | 0 bytes | 這是一條備注
(1 row)
teledb=# comment on column teledb_t1.mc is '這是一條字段注釋';
COMMENT
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+------------------
id | integer | | | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES給表增加字段
teledb=# alter table teledb_t1 add column age integer;
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+------------------
id | integer | | | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
age | integer | | | | plain | |
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES修改字段類型
teledb=# alter table teledb_t1 alter column age type float8;
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------+-----------+----------+---------+----------+--------------+------------------
id | integer | | | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
age | double precision | | | | plain | |
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES修改字段默認值
teledb=# alter table teledb_t1 alter column age set default 0.0;
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------+-----------+----------+---------+----------+--------------+------------------
id | integer | | | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
age | double precision | | | 0.0 | plain | |
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES刪除字段
teledb=# alter table teledb_t1 drop column age;
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+------------------
id | integer | | | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES添加主鍵
teledb=# alter table teledb_t1 add constraint teledb_id_pkey primary key(id);
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+------------------
id | integer | | not null | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
Indexes:
"teledb_id_pkey" PRIMARY KEY, btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES刪除主鍵
teledb=# alter table teledb_t1 drop constraint teledb_id_pkey;
ALTER TABLE
teledb=# \d+ teledb_t1
Table "public.teledb_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+------------------
id | integer | | not null | | plain | |
mc | text | | | | extended | | 這是一條字段注釋
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES如果是分區表，則刪除主鍵要加上cascade，強制刪除關聯的子表主鍵。
重建主鍵
teledb=# create table t(id int primary key, mc text);
CREATE TABLE
teledb=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
mc | text | | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
teledb=# CREATE UNIQUE INDEX CONCURRENTLY t_id_temp_idx ON t (id);
CREATE INDEX
teledb=# ALTER TABLE t DROP CONSTRAINT t_pkey, ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX t_id_temp_idx;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "t_id_temp_idx" to "t_pkey"
ALTER TABLE
teledb=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
mc | text | | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)添加外鍵
teledb=# create table t_p(f1 int not null,f2 int ,primary key(f1));
CREATE TABLE
teledb=# create table t_f(f1 int not null,f2 int );
CREATE TABLE
teledb=# ALTER TABLE t_f ADD CONSTRAINT t_f_f1_fkey FOREIGN KEY (f1) REFERENCES t_p (f1);
ALTER TABLE
teledb=# \d+ t_f
Table "public.t_f"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
f1 | integer | | not null | | plain | |
f2 | integer | | | | plain | |
Foreign-key constraints:
"t_f_f1_fkey" FOREIGN KEY (f1) REFERENCES t_p(f1)
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES外鍵使用限制：
外鍵只是同一個節點內約束有效果，所以外鍵字段和對應主鍵字段必需都是表的分布鍵，否則由于數據分布于不同的節點內會導致更新失敗。
分區表和冷熱分區表也不支持外鍵，數據分區后位于不同的物理文件中，無法約束。
刪除外鍵
teledb=# ALTER TABLE t_f DROP CONSTRAINT t_f_f1_fkey;
ALTER TABLE修改表所屬模式
teledb=# create schema teledb;
CREATE SCHEMA
teledb=# \dt t
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | t | table | teledb
(1 row)
teledb=# alter table t set schema teledb;
ALTER TABLE
teledb=# \dt t
List of relations
Schema | Name | Type | Owner
---------+------+-------+---------
teledb | t | table | teledb
(1 row)修改表所屬用戶
teledb=# \dt t
List of relations
Schema | Name | Type | Owner
---------+------+-------+---------
teledb | t | table | teledb
(1 row)
teledb=# alter table t owner to user1;
ALTER TABLE
teledb=# \dt t
List of relations
Schema | Name | Type | Owner
---------+------+-------+-------
teledb | t | table | user1
(1 row)修改字段名
teledb=# \d+ t
Table "teledb.t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
mc | text | | | | extended | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES
teledb=# alter table t rename mc to nickname;
ALTER TABLE
teledb=# \d+ t
Table "teledb.t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
nickname | text | | | | extended | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES
