修改表结构
更新时间 2025-02-14 10:22:09
最近更新时间: 2025-02-14 10:22:09
您可以根据实际情况修改表，例如修改表名、给表或字段添加注释、给表增加字段、修改字段类型、修改字段默认值、删除字段和管理主键等。
修改表名
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
