TDSQL PostgreSQL版
有奖捉虫：行业应用 & 管理与支持文档专题 HOT
文档中心 > TDSQL PostgreSQL版 > 开发指南 > 数据库基础开发 > 库模式表索引视图物化视图等 DDL 操作 > 修改表结构

修改表结构

最近更新时间：2022-09-02 16:25:35

我的收藏

本页目录：

修改表名

postgres=# alter table t rename to tdsql_pg;
ALTER TABLE

给表或字段添加注释

postgres=# comment on table tdsql_pg is ' TDSQL PostgreSQL分布式关系型数据库系统';
COMMENT
postgres=# \\dt+
                List of relations
 Schema |    Name     | Type  | Owner | Size  |     Description     
--------+-------------------+-------+-------+-------+-----------------------------
 public | t_appoint_col   | table | pgxz  | 16 kB | 
 public | t_first_col_share | table | pgxz  | 16 kB | 
 public | tdsql_pg       | table | pgxz  | 24 kB |  TDSQL PostgreSQL分布式关系型数据库系统
(3 rows)
postgres=# comment on column tdsql_pg.nickname is ' TDSQL PostgreSQL呢称是大象';
COMMENT
postgres=# \\d+ tdsql_pg
                       Table "public.tdsql_pg"
 Column  |  Type  |          Modifiers           | Storage  | Stats target |  Description  
----------+---------+------------------------------------------------+----------+--------------+-----------------
 id    | integer | not null default nextval('t_id_seq'::regclass) | plain   |        | 
 nickname | text   |                         | extended |        |  TDSQL PG版呢称是大象
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES

postgres=#

给表增加字段

postgres=# alter table tdsql_pg add column age integer;
ALTER TABLE
postgres=# \\d+ tdsql_pg
                       Table "public.tdsql_pg"
 Column  |  Type  |          Modifiers           | Storage  | Stats target |  Description  
----------+---------+------------------------------------------------+----------+--------------+-----------------
 id    | integer | not null default nextval('t_id_seq'::regclass) | plain   |        | 
 nickname | text   |                         | extended |        |  TDSQL PG版呢称是大象
 age    | integer |                         | plain   |        | 
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES

修改字段类型

postgres=# alter table tdsql_pg alter column age type float8;   
ALTER TABLE
postgres=#  \\d+ tdsql_pg
                          Table "public.tdsql_pg"
 Column  |    Type    |          Modifiers           | Storage  | Stats target |  Description  
----------+------------------+------------------------------------------------+----------+--------------+-----------------
 id    | integer      | not null default nextval('t_id_seq'::regclass) | plain   |        | 
 nickname | text       |                         | extended |        |  TDSQL PG版呢称是大象
 age    | double precision |                         | plain   |        | 
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES

postgres=#

修改字段默认值

postgres=# alter table tdsql_pg alter column age set default 0.0;
ALTER TABLE
postgres=#  \\d+ tdsql_pg
                          Table "public.tdsql_pg"
 Column  |    Type    |          Modifiers           | Storage  | Stats target |  Description  
----------+------------------+------------------------------------------------+----------+--------------+-----------------
 id    | integer      | not null default nextval('t_id_seq'::regclass) | plain   |        | 
 nickname | text       |                         | extended |        |  TDSQL PG版呢称是大象
 age    | double precision | default 0.0                  | plain   |        | 
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES

删除字段

postgres=# alter table tdsql_pg drop column age;
ALTER TABLE
postgres=#  \\d+ tdsql_pg
                       Table "public.tdsql_pg"
 Column  |  Type  |          Modifiers           | Storage  | Stats target |  Description  
----------+---------+------------------------------------------------+----------+--------------+-----------------
 id    | integer | not null default nextval('t_id_seq'::regclass) | plain   |        | 
 nickname | text   |                         | extended |        |  TDSQL PG版呢称是大象
Has OIDs: no
Distribute By SHARD(id)
    Location Nodes: ALL DATANODES

添加主键

postgres=# ALTER TABLE t ADD CONSTRAINT t_id_pkey PRIMARY KEY (id) ;
ALTER TABLE
postgres=# \\d+ t
                   Table "tdsql_pg.t"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id   | integer |      | not null |     | plain   |        | 
 mc   | text   |      |      |     | extended |        | 
Indexes:
  "t_id_pkey" PRIMARY KEY, btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES

删除主键

postgres=# ALTER TABLE t DROP CONSTRAINT t_id_pkey ;
ALTER TABLE
postgres=# \\d+ t
                   Table "tdsql_pg.t"
 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，强制删除关联的子表主键。

重建主键

postgres=# \\d t
         Table "public.t"
 Column |  Type  | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id   | integer |      | not null | 
 mc   | text   |      |      | 
Indexes:
"t_pkey" PRIMARY KEY, btree (id)

postgres=# CREATE UNIQUE INDEX CONCURRENTLY t_id_temp_idx ON t (id);      
CREATE INDEX
postgres=#
postgres=# \\d t
         Table "public.t"
 Column |  Type  | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id   | integer |      | not null | 
 mc   | text   |      |      | 
Indexes:
  "t_pkey" PRIMARY KEY, btree (id)
  "t_id_temp_idx" UNIQUE, btree (id)

postgres=#

postgres=# 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
postgres=#
postgres=# \\d t
         Table "public.t"
 Column |  Type  | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id   | integer |      | not null | 
 mc   | text   |      |      | 
Indexes:
  "t_pkey" PRIMARY KEY, btree (id)

postgres=#

添加外键

create table t_p(f1 int not null,f2 int ,primary key(f1));
create table t_f(f1 int not null,f2 int );
postgres=# ALTER TABLE t_f ADD CONSTRAINT t_f_f1_fkey FOREIGN KEY (f1) REFERENCES t_p (f1);
ALTER TABLE
postgres=# \\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

外键使用限制

外键只是同一个节点内约束有效果，所以外键字段和对应主键字段必需都是表的分布键，否则由于数据分布于不同的节点内会导致更新失败。
分区表和冷热分区表也不支持外键，数据分区后位于不同的物理文件中，无法约束。

删除外键

postgres=# ALTER TABLE t_f DROP  CONSTRAINT t_f_f1_fkey;  
ALTER TABLE
postgres=#

修改表所属模式

postgres=# \\dt t
    List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 tdsql_pg | t   | table | dbadmin
(1 row)

postgres=# alter table t set schema public;
ALTER TABLE

postgres=# \\dt t
    List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t   | table | dbadmin
(1 row)

修改表所属用户

postgres=# \\dt tdsql_pg
    List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | tdsql_pg | table | dbadmin
(1 row)

postgres=# alter table tdsql_pg owner to pgxz;
ALTER TABLE

postgres=# \\dt tdsql_pg
    List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | tdsql_pg | table | pgxz
(1 row)

修改字段名

postgres=# \\d+ tdsql_pg
                     Table "public.tdsql_pg"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id   | integer        |      | not null |     | plain   |        | 
 city  | character varying(50) |      |      |     | extended |        | 
Distribute By: SHARD(id)
Location Nodes: dn01

postgres=# alter table tdsql_pg rename city to cityname;   
ALTER TABLE

postgres=# \\d+ tdsql_pg
                      Table "public.tdsql_pg"
 Column  |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id    | integer        |      | not null |     | plain   |        | 
 cityname | character varying(50) |      |      |     | extended |        | 
Distribute By: SHARD(id)
Location Nodes: dn01

中国站