跳转至

修改表

修改表

当我们已经创建了一个表，但意识到犯了错误或者应用需求发生改变时，我们可以移除表并重新创建它。但如果表中已经被填充数据或者被其他数据库对象引用（例如有外键约束），这种做法就显得很不方便。因此，SeaboxSQL提供了一组命令来对已有的表进行修改。注意这和修改表中所包含的数据是不同的，这里要做的是对表的定义或者说结构进行修改。

利用这些命令，我们可以：

  • 增加列

  • 移除列

  • 增加约束

  • 移除约束

  • 修改默认值

  • 修改列数据类型

  • 重命名列

  • 重命名表

  • 修改表参数

所有这些动作都由ALTER TABLE命令执行，其参考页面中包含更详细的信息。

增加列

要增加一个列，可以使用这样的命令：

ALTER TABLE products ADD COLUMN description text;

新列将被默认值所填充（如果没有指定DEFAULT子句，则会填充空值）。

也可以同时为列定义约束，语法：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

事实上CREATE TABLE中关于列的描述都可以应用在这里。无论如何，默认值必须满足给定的约束，否则ADD将会失败。也可以先将新列正确地填充好，然后再增加约束（见后文）。

提示

增加一个带默认值的列需要更新表中的每一行（来存储新列值）。然而，如果不指定默认值，SeaboxSQL可以避免物理更新。因此如果我们准备向列中填充的值大多是非默认值，最好是增加列的时候不指定默认值，增加列后用UPDATE填充正确的数据并且增加所需要的默认值约束。

移除列

为了移除一个列，使用如下的命令：

ALTER TABLE products DROP COLUMN description;

列中的数据将会消失。涉及到该列的表约束也会被移除。同时，如果该列被另一个表的外键所引用，SeaboxSQL直接移除该约束。我们可以通过增加CASCADE来授权移除任何依赖于被删除列的所有记录：

ALTER TABLE products DROP COLUMN description CASCADE;

关于这个操作背后的一般性机制请见依赖跟踪。

增加约束

为了增加一个约束，可以使用表约束的语法，例如：

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

要增加一个不能写成表约束的非空约束，可使用语法：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束。

移除约束

为了移除一个约束首先需要知道它的名称。如果在创建时已经给它指定了名称，那么事情就变得很容易。否则约束的名称是由系统生成的，我们必须先找出这个名称。ssql的命令\d 表名将会对此有所帮助，其他接口也会提供方法来查看表的细节。因此命令是：

ALTER TABLE products DROP CONSTRAINT some_name;

（如果处理的是自动生成的约束名称，如$2，别忘了用双引号使它变成一个合法的标识符。）

和移除列相似，如果需要移除约束也需要加上CASCADE。一个例子是一个外键约束依赖于被引用列上的一个唯一或者主键约束。

这对除了非空约束之外的所有约束类型都一样有效。为了移除一个非空约束可以用：

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

更改列的默认值

要为一个列设置一个新默认值，使用命令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意这不会影响任何表中已经存在的行，它只是为未来的INSERT命令改变了默认值。

要移除任何默认值，使用：

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这等同于将默认值设置为空值。相应的，试图删除一个未被定义的默认值并不会引发错误，因为默认值已经被隐式地设置为空值。

修改列的数据类型

为了将一个列转换为一种不同的数据类型，使用如下命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。

SeaboxSQL将尝试把列的默认值转换为新类型，其他涉及到该列的任何约束也是一样。但是这些转换可能失败，因此最好在修改类型之前先删除该列上所有的约束，然后在修改完类型后重新加上相应修改过的约束。

重命名列

要重命名一个列：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

要重命名一个表：

ALTER TABLE products RENAME TO items;

修改表参数

表的COUNTER功能

表增加counter开关, 是为了优化select count(*)查询性能, 避免全表扫描

  • 语法支持 

    -- 建表时指定counter
CREATE TABLE T1(a INT) WITH(COUNTER);
-- 修改表counter属性
-- 打开
ALTER TABLE T1 SET (COUNTER = 0);
-- 关闭
ALTER TABLE T1 SET (COUNTER = 1);

  • insert/delete

  • 表的counter开关打开后, 针对表进行insert/delete操作时,会将本次操作的count值记录到系统表sd_rel_countinfo里
  • update操作不处理

  • 系统表sd_rel_countinfo 

    CATALOG(sd_rel_countinfo,7176,Sdrelcountinfo_RelationId)
{
    Oid			oid;    //rel oid
    TransactionId xact_id; //cur transactionid
    int16   operation; //insert:3, delete:4
    int64   count;  
}FormData_sd_rel_countinfo;

  • select count(*)

  • 查询执行select count(*) from tab时, 如果表的counter开关打开, 则会调用relation_count()函数去查询系统表里记录的值并返回

  • 查询能够走优化的条件

    • 表的counter开关打开
    • 查询count优化开关query_count_from_system为ON, 当前默认为ON

  • 示例 

    -- 建表插入数据
seaboxsql=# create table t1(a int);
CREATE TABLE
seaboxsql=# insert into t1 select g from generate_series(1, 20)g;
INSERT 0 20
-- 打开counter
seaboxsql=# alter table t1 set (counter);
ALTER TABLE
seaboxsql=# \d+ t1
                                            Table "public.t1"
Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Char Semantics | Description 
--------+---------+-----------+----------+---------+---------+--------------+----------------+-------------
a      | integer |           |          |         | plain   |              | none           | 
Access method: heap
Options: counter=true

seaboxsql=# select b.relname, a.* from sd_rel_countinfo a, pg_class b where a.oid = b.oid;
relname |  oid   | xact_id | operation | count 
---------+--------+---------+-----------+-------
t1      | 211003 |   95624 |         3 |    20
(1 row)  
-- 执行insert&更新系统表
seaboxsql=# insert into t1 select * from t1;
INSERT 0 20
seaboxsql=# select b.relname, a.* from sd_rel_countinfo a, pg_class b where a.oid = b.oid;
relname |  oid   | xact_id | operation | count 
---------+--------+---------+-----------+-------
t1      | 211003 |   95624 |         3 |    20
t1      | 211003 |   95625 |         3 |    20
(2 rows)
-- 执行delete&更新系统表
seaboxsql=# delete from t1 where a % 3 = 0;
DELETE 12
seaboxsql=# select b.relname, a.* from sd_rel_countinfo a, pg_class b where a.oid = b.oid;
relname |  oid   | xact_id | operation | count 
---------+--------+---------+-----------+-------
t1      | 211003 |   95624 |         3 |    20
t1      | 211003 |   95625 |         3 |    20
t1      | 211003 |   95626 |         4 |    12
(3 rows)
-- 执行update 不更新系统表
seaboxsql=# update t1 set a = a+1 where a % 5 = 0;
UPDATE 6
seaboxsql=# select b.relname, a.* from sd_rel_countinfo a, pg_class b where a.oid = b.oid;
relname |  oid   | xact_id | operation | count 
---------+--------+---------+-----------+-------
t1      | 211003 |   95624 |         3 |    20
t1      | 211003 |   95625 |         3 |    20
t1      | 211003 |   95626 |         4 |    12
(3 rows)

-- select count(*)查询
seaboxsql=# show query_count_from_system ;
query_count_from_system 
-------------------------
on
(1 row)
-- 查询count优化打开, 且表的counter开关打开后, select count(*) 操作不会扫描全表
seaboxsql=# explain select count(*) from t1;
                QUERY PLAN                
------------------------------------------
Result  (cost=0.00..0.01 rows=1 width=8)
(1 row)

seaboxsql=# select count(*) from t1;
count 
-------
    28
(1 row)

-- 关闭查询count优化
seaboxsql=# set query_count_from_system = 0;
SET
seaboxsql=# show query_count_from_system ;
query_count_from_system 
-------------------------
off
(1 row)
--关闭后, select count(*) 操作会扫描全表
seaboxsql=# explain select count(*) from t1;
                          QUERY PLAN                          
--------------------------------------------------------------
Aggregate  (cost=137.88..137.88 rows=1 width=8)
  ->  Seq Scan on t1  (cost=0.00..112.30 rows=10230 width=0)
(2 rows)

seaboxsql=# select count(*) from t1;
count 
-------
    28
(1 row)

seaboxsql=# set query_count_from_system = 1;
SET

--关闭counter
seaboxsql=# alter table t1 set (counter = 0);
ALTER TABLE

--关闭后, select count(*) 操作会扫描全表
seaboxsql=# explain select count(*) from t1;
                          QUERY PLAN                          
--------------------------------------------------------------
Aggregate  (cost=137.88..137.88 rows=1 width=8)
  ->  Seq Scan on t1  (cost=0.00..112.30 rows=10230 width=0)
(2 rows)