修改表结构

最近更新时间:2019-12-03 17:10:59

修改表名

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

给表或字段添加注释

postgres=# comment on table tbase is 'TBase分布式关系型数据库系统';
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 | TBase             | table | pgxz  | 24 kB | TBase分布式关系型数据库系统
(3 rows)
postgres=# comment on column tbase.nickname is 'TBase呢称是大象';
COMMENT
postgres=# \d+ tbase
                                              Table "public.tbase"
  Column  |  Type   |                   Modifiers                    | Storage  | Stats target |   Description   
----------+---------+------------------------------------------------+----------+--------------+-----------------
 id       | integer | not null default nextval('t_id_seq'::regclass) | plain    |              | 
 nickname | text    |                                                | extended |              | TBase呢称是大象
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

postgres=# 

给表增加字段

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

修改字段类型

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

postgres=# 

修改字段默认值

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

删除字段

postgres=# alter table tbase drop column age;
ALTER TABLE
postgres=#  \d+ tbase
                                              Table "public.tbase"
  Column  |  Type   |                   Modifiers                    | Storage  | Stats target |   Description   
----------+---------+------------------------------------------------+----------+--------------+-----------------
 id       | integer | not null default nextval('t_id_seq'::regclass) | plain    |              | 
 nickname | text    |                                                | extended |              | TBase呢称是大象
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 "tbase.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 "tbase.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

添加外键

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 
--------+------+-------+-------
 tbase  | t    | table | tbase
(1 row)

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

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

修改表所属用户

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

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

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