修改表名
postgres=# alter table t rename to tdsql_pg;ALTER TABLE
给表或字段添加注释
postgres=# comment on table tdsql_pg is ' TDSQL PostgreSQL分布式关系型数据库系统';COMMENTpostgres=# \\dt+List of relationsSchema | 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_pgTable "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_pgTable "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_pgTable "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_pgTable "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_pgTable "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+ tTable "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+ tTable "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 tTable "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 tTable "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 tTable "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_fTable "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 tList of relationsSchema | Name | Type | Owner--------+------+-------+-------tdsql_pg | t | table | dbadmin(1 row)postgres=# alter table t set schema public;ALTER TABLEpostgres=# \\dt tList of relationsSchema | Name | Type | Owner--------+------+-------+-------public | t | table | dbadmin(1 row)
修改表所属用户
postgres=# \\dt tdsql_pgList of relationsSchema | Name | Type | Owner--------+-------+-------+-------public | tdsql_pg | table | dbadmin(1 row)postgres=# alter table tdsql_pg owner to pgxz;ALTER TABLEpostgres=# \\dt tdsql_pgList of relationsSchema | Name | Type | Owner--------+-------+-------+-------public | tdsql_pg | table | pgxz(1 row)
修改字段名
postgres=# \\d+ tdsql_pgTable "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_pgTable "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