一、给表或字段添加注释
postgres=# comment on table tdsql_pg is ' TDSQL PostgreSQL分布式关系型数据库系统';COMMENTpostgres=# \\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呢称是大象';COMMENTpostgres=# \\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: noDistribute By SHARD(id) Location Nodes: ALL DATANODESpostgres=#
二、给表增加字段
postgres=# alter table tdsql_pg add column age integer;ALTER TABLEpostgres=# \\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: noDistribute By SHARD(id) Location Nodes: ALL DATANODES
三、修改字段类型
postgres=# alter table tdsql_pg alter column age type float8; ALTER TABLEpostgres=# \\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: noDistribute By SHARD(id) Location Nodes: ALL DATANODESpostgres=#
四、修改字段默认值
postgres=# alter table tdsql_pg alter column age set default 0.0;ALTER TABLEpostgres=# \\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: noDistribute By SHARD(id) Location Nodes: ALL DATANODES
五、删除字段
postgres=# alter table tdsql_pg drop column age;ALTER TABLEpostgres=# \\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: noDistribute By SHARD(id) Location Nodes: ALL DATANODES
六、添加主键
postgres=# ALTER TABLE t ADD CONSTRAINT t_id_pkey PRIMARY KEY (id) ;ALTER TABLEpostgres=# \\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 TABLEpostgres=# \\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 INDEXpostgres=#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 TABLEpostgres=#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 TABLEpostgres=# \\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 TABLEpostgres=#
十二、修改表所属模式
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 TABLEpostgres=# \\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 TABLEpostgres=# \\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: dn01postgres=# alter table tdsql_pg rename city to cityname; ALTER TABLEpostgres=# \\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