修改表名

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 TABL

修改表所属模式

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