这是本节的多页打印视图。点击此处打印.

管理表列

1: 重命名列
2: 更改列数据类型

2.1: 更改列宽
2.2: 使用列数据转换

3: 定义列值

在定义表之后，可以使用 ALTER TABLE 修改现有表列。可以对列执行以下操作：

1 - 重命名列

使用 ALTER TABLE 重命名列，如下所示：

ALTER TABLE [schema.]table-name  RENAME [ COLUMN ] column-name TO new-column-name

以下示例将 Retail.Product_Dimension 表中的列从 Product_description 重命名为 Item_description：

=> ALTER TABLE Retail.Product_Dimension
    RENAME COLUMN Product_description TO Item_description;

如果重命名视图引用的列，该列不会显示在视图的结果集中，即使视图使用通配符 (*) 来表示表中的所有列。重新创建视图以合并列的新名称。

2 - 更改列数据类型

通常，可以使用 ALTER TABLE 更改列的数据类型（如果这样做不需要重新组织存储）。修改列的数据类型后，加载的数据符合新定义。

以下几个部分介绍与更改列的数据类型相关的要求和限制。

支持的数据类型转换

Vertica 支持转换以下数据类型：

不支持的数据类型转换

Vertica 不允许对需要重新组织存储的类型进行数据类型转换：

Boolean
日期/时间
近似数字类型
BINARY 到 VARBINARY，反之亦然

如果列属于以下情况之一，也不能更改列的数据类型：

主键
外键
包含在该表的任何投影的 SEGMENTED BY 子句中。
复杂类型列。有一个例外：在外部表中，可以将基元列类型更改为复杂类型。

可以绕过其中一些限制。有关详细信息，请参阅使用列数据转换。

2.1 - 更改列宽

可以扩展同一类数据类型中的列。这样做对于在列中存储较大的项目很有用。Vertica 在执行转换之前会验证数据。

通常，还可以减小数据类型类中的列宽。如果原始声明的长度比您需要的要长，这对于回收存储很有用，特别是对于字符串。仅当满足以下条件时，才能减小列宽：

现有列数据不大于新宽度。
数据库群集中的所有节点都已启动。

否则，Vertica 会返回错误并且转换失败。例如，如果尝试将列从 varchar(25) 转换为 varchar(10)，只要所有列数据不超过 10 个字符，Vertica 就允许转换。

在以下示例中，列 y 和 z 最初定义为 VARCHAR 数据类型，并分别加载值 12345 和 654321。将列 z 的宽度减小到 5 的尝试失败，因为它包含六个字符的数据。将列 y 的宽度减小到 5 的尝试成功，因为它的内容符合新的宽度：

=> CREATE TABLE t (x int, y VARCHAR, z VARCHAR);
CREATE TABLE
=> CREATE PROJECTION t_p1 AS SELECT * FROM t SEGMENTED BY hash(x) ALL NODES;
CREATE PROJECTION
=> INSERT INTO t values(1,'12345','654321');
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM t;
 x |   y   |   z
---+-------+--------
 1 | 12345 | 654321
(1 row)

=> ALTER TABLE t ALTER COLUMN z SET DATA TYPE char(5);
ROLLBACK 2378:  Cannot convert column "z" to type "char(5)"
HINT:  Verify that the data in the column conforms to the new type
=> ALTER TABLE t ALTER COLUMN y SET DATA TYPE char(5);
ALTER TABLE

更改集合列

如果列是集合数据类型，则可以使用 ALTER TABLE 更改其边界或最大二进制大小。这些属性是在表创建时设置的，随后可以更改。

您可以使集合有界，设置其最大元素数，如下面的示例所示。

=> ALTER TABLE test.t1 ALTER COLUMN arr SET DATA TYPE array[int,10];
ALTER TABLE

=> \d test.t1
                                     List of Fields by Tables
 Schema | Table | Column |      Type       | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------------+------+---------+----------+-------------+-------------
  test  |  t1   | arr    | array[int8, 10] |   80 |         | f        | f           |
(1 row)

或者，可以设置整个集合的二进制大小，而不是设置边界。二进制大小是显式设置的，也可以通过 DefaultArrayBinarySize 配置参数设置。下面的示例从默认值创建一个数组列，更改默认值，然后使用 ALTER TABLE 将其更改为新的默认值。

=> SELECT get_config_parameter('DefaultArrayBinarySize');
 get_config_parameter
----------------------
 100
(1 row)

=> CREATE TABLE test.t1 (arr array[int]);
CREATE TABLE

=> \d test.t1
                                     List of Fields by Tables
 Schema | Table | Column |      Type       | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------------+------+---------+----------+-------------+-------------
  test  |  t1   | arr    | array[int8](96) |   96 |         | f        | f           |
(1 row)

=> ALTER DATABASE DEFAULT SET DefaultArrayBinarySize=200;
ALTER DATABASE

=> ALTER TABLE test.t1 ALTER COLUMN arr SET DATA TYPE array[int];
ALTER TABLE

=> \d test.t1
                                     List of Fields by Tables
 Schema | Table | Column |      Type       | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------------+------+---------+----------+-------------+-------------
  test  |  t1   | arr    | array[int8](200)|  200 |         | f        | f           |
(1 row)

或者，可以显式设置二进制大小，而不是使用默认值。

=> ALTER TABLE test.t1 ALTER COLUMN arr SET DATA TYPE array[int](300);

清除历史数据

如果 Vertica 保留任何超过新宽度的历史数据，则无法减小列的宽度。要减小列宽，首先从表中移除该数据：

将 AHM 推进到比需要从表中移除的历史数据更近的时期。
使用函数 PURGE_TABLE 清除表中 AHM 之前的所有历史数据。

例如，在前面的示例中，您可以更新 t.z 列中的数据，如下所示：

=> UPDATE t SET z = '54321';
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM t;
 x |   y   |   z
---+-------+-------
 1 | 12345 | 54321
(1 row)

尽管现在 z 列中没有数据超过 5 个字符，但 Vertica 保留了其早期数据的历史记录，因此尝试将列宽减小到 5 会返回错误：

=> ALTER TABLE t ALTER COLUMN z SET DATA TYPE char(5);
ROLLBACK 2378:  Cannot convert column "z" to type "char(5)"
HINT:  Verify that the data in the column conforms to the new type

您可以通过清除表的历史数据来减小列宽，如下所示：

=> SELECT MAKE_AHM_NOW();
         MAKE_AHM_NOW
-------------------------------
 AHM set (New AHM Epoch: 6350)
(1 row)

=> SELECT PURGE_TABLE('t');
                                                     PURGE_TABLE
----------------------------------------------------------------------------------------------------------------------
 Task: purge operation
(Table: public.t) (Projection: public.t_p1_b0)
(Table: public.t) (Projection: public.t_p1_b1)

(1 row)


=> ALTER TABLE t ALTER COLUMN z SET DATA TYPE char(5);
ALTER TABLE

2.2 - 使用列数据转换

Vertica 通过禁止对表列进行某些数据转换来符合 SQL 标准。但是，当从非 SQL 数据库转换数据时，有时需要绕过此限制。以下示例描述了一种这样的解决方法，使用下表：

=> CREATE TABLE sales(id INT, price VARCHAR) UNSEGMENTED ALL NODES;
CREATE TABLE
=> INSERT INTO sales VALUES (1, '$50.00');
 OUTPUT
--------
      1
(1 row)

=> INSERT INTO sales VALUES (2, '$100.00');
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT

=> SELECT * FROM SALES;
 id |  price
----+---------
  1 | $50.00
  2 | $100.00
(2 rows)

要将 price 列的现有数据类型从 VARCHAR 转换为 NUMERIC，请完成以下步骤：

添加一个新列供临时使用。为该列分配 NUMERIC 数据类型，并从现有 price 列中派生其默认值。
删除原始 price 列。
将新列重命名为原始列。

添加一个新列供临时使用

将列 temp_price 添加到表 sales。可以暂时使用新列，将其数据类型设置为所需的数据类型 (NUMERIC)，并从 price 列中派生其默认值。将新列的默认值转换为 NUMERIC 数据类型并查询该表：

=> ALTER TABLE sales ADD COLUMN temp_price NUMERIC(10,2) DEFAULT
SUBSTR(sales.price, 2)::NUMERIC;
ALTER TABLE

=> SELECT * FROM SALES;
 id |  price  | temp_price
----+---------+------------
  1 | $50.00  |      50.00
  2 | $100.00 |     100.00
(2 rows)

使用 ALTER TABLE 从新列 temp_price 中删除默认表达式。Vertica 会保留存储在此列中的值：
```
=> ALTER TABLE sales ALTER COLUMN temp_price DROP DEFAULT;
ALTER TABLE
```

删除原始 price 列

删除无关的 price 列。在这样做之前，必须先推进 AHM 以清除会阻止删除操作的历史数据：

推进 AHM：

=> SELECT MAKE_AHM_NOW();
         MAKE_AHM_NOW
-------------------------------
 AHM set (New AHM Epoch: 6354)
(1 row)

删除原始 price 列：

=> ALTER TABLE sales DROP COLUMN price CASCADE;
ALTER COLUMN

将新列重命名为原始列

现在可以将 temp_price 列重命名为 price：

使用 ALTER TABLE 重命名列：

=> ALTER TABLE sales RENAME COLUMN temp_price to price;

再次查询 sales 表：

=> SELECT * FROM sales;
 id | price
----+--------
  1 |  50.00
  2 | 100.00
(2 rows)

3 - 定义列值

您可以定义一个列，以便 Vertica 通过以下子句之一从表达式自动设置其值：

DEFAULT
SET USING
DEFAULT USING

DEFAULT

DEFAULT 选项将列值设置为指定值。它具有以下语法：

DEFAULT default-expression

当您执行以下操作时会设置 Default 值：

将新行加载到表中，例如，使用 INSERT 或 COPY。Vertica 使用默认值填充新行中的 DEFAULT 列。现有行中的值（包括具有 DEFAULT 表达式的列）保持不变。
对表执行 UPDATE，并将 DEFAULT 列的值设置为 DEFAULT：
```
=> UPDATE table-name SET column-name=DEFAULT;
```
将具有 DEFAULT 表达式的列添加到现有表。将新列添加到表中时，Vertica 会使用其默认值填充新列。

注意
更改现有表列以指定 DEFAULT 表达式对该列中的现有值没有影响。通过 INSERT 和 COPY 等加载操作将新行添加到表中时，Vertica 将 DEFAULT 表达式仅应用于新行。要使用列的 DEFAULT 表达式刷新列中的所有值，请如上所示更新列。

限制

DEFAULT 表达式无法使用 ALTER TABLE...ADD COLUMN 指定易变函数。要指定易变函数，请使用 CREATE TABLE 或 ALTER TABLE...ALTER COLUMN 语句。

SET USING

当对列调用 REFRESH_COLUMNS 函数时，SET USING 选项将该列值设置为表达式。此选项具有以下语法：

SET USING using-expression

这种方法对于大型非标准化（扁平）表很有用，其中多个列通过查询其他表来获取它们的值。

限制

SET USING 具有以下限制：

不允许使用易变函数。
表达式不能指定序列。
Vertica 限制了复制表数据的多个元函数的使用：COPY_TABLE、COPY_PARTITIONS_TO_TABLE、MOVE_PARTITIONS_TO_TABLE 和 SWAP_PARTITIONS_BETWEEN_TABLES：
- 如果源表和目标表都具有 SET USING 列，则仅当每个源 SET USING 列具有相应的目标 SET USING 列时，才允许执行该操作。
- 如果只有源表具有 SET USING 列，则不允许使用 SWAP_PARTITIONS_BETWEEN_TABLES。
- 如果只有目标表具有 SET USING 列，则不允许执行该操作。

DEFAULT USING

DEFAULT USING 选项对列设置 DEFAULT 和 SET USING 约束，相当于对同一列分别使用 DEFAULT 和 SET USING 执行相同的表达式。它具有以下语法：

DEFAULT USING expression

例如，以下列定义实际上是相同的：

=> ALTER TABLE public.orderFact ADD COLUMN cust_name varchar(20)
     DEFAULT USING (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid));

=> ALTER TABLE public.orderFact ADD COLUMN cust_name varchar(20)
     DEFAULT (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid))
     SET USING (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid));

DEFAULT USING 支持与 SET USING 相同的表达式，并受到相同的限制。

通常，DEFAULT 和 SET USING 支持相同的表达式。这些包括：

查询
同一个表的其他列
字面量（常数）
Vertica 支持的所有运算符
以下函数类别：

表达式限制

以下限制适用于 DEFAULT 和 SET USING 表达式：

返回值数据类型必须与列数据类型匹配，或强制转换为列数据类型。
表达式必须返回一个符合列边界的值。例如，定义为 VARCHAR(1) 的列不能设置为默认字符串 abc。
在临时表中，DEFAULT 和 SET USING 不支持子查询。如果尝试创建一个临时表，其中 DEFAULT 或 SET USING 使用子查询表达式，Vertica 会返回错误。
一个列的 SET USING 表达式不能在同一个表中指定另一个也使用 SET USING 设置其值的列。同样，一个列的 DEFAULT 表达式不能在同一个表中指定另一个也使用 DEFAULT 设置其值的列，或者其值自动设置为序列的列。但是，一个列的 SET USING 表达式可以指定另一个使用 DEFAULT 设置其值的列。

注意
可以从同一个表中另一个使用 SET USING 设置其值的列，来设置一个列的 DEFAULT 表达式。但是，DEFAULT 列通常设置为 NULL，因为它仅在最初将 SET USING 列设置为 NULL 的加载操作上设置。

DEFAULT 和 SET USING 表达式仅支持一个 SELECT 语句；尝试在该表达式中包含多个 SELECT 语句会返回错误。例如，给定表 t1：

=> SELECT * FROM t1;
 a |    b
---+---------
 1 | hello
 2 | world
(2 rows)

尝试使用以下 DEFAULT 表达式创建表 t2 会返回错误：

=> CREATE TABLE t2 (aa int, bb varchar(30) DEFAULT (SELECT 'I said ')||(SELECT b FROM t1 where t1.a = t2.aa));
ERROR 9745:  Expressions with multiple SELECT statements cannot be used in 'set using' query definitions

消除谓词列的歧义

如果 SET USING 或 DEFAULT 查询表达式联接两个同名的列，则列名称必须包含它们的表名称。否则，Vertica 假定两个列都引用维度表，并且谓词的计算结果始终为 true。

例如，表 orderFact 和 custDim 都包含 cid 列。扁平表 orderFact 使用 SET USING 查询表达式定义列 cust_name。由于查询谓词引用两个表中的 cid 列，因此列名称是完全限定的：

=> CREATE TABLE public.orderFact
(
    ...
    cid int REFERENCES public.custDim(cid),
    cust_name varchar(20) SET USING (
        SELECT name FROM public.custDim WHERE (custDIM.cid = orderFact.cid)),
    ...
)

示例

从另一个列派生一个列的默认值

创建表 t，其中包含两个列 date 和 state，并插入一行数据：

=> CREATE TABLE t (date DATE, state VARCHAR(2));
CREATE TABLE
=> INSERT INTO t VALUES (CURRENT_DATE, 'MA');
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMMIT
SELECT * FROM t;
    date    | state
------------+-------
 2017-12-28 | MA
(1 row)

使用 ALTER TABLE 添加第三个列，它从列 date 中提取整数月份值：

=> ALTER TABLE t ADD COLUMN month INTEGER DEFAULT date_part('month', date);
ALTER TABLE

当查询表 t 时，Vertica 会在列 date 中返回月份数：

=> SELECT * FROM t;
    date    | state | month
------------+-------+-------
 2017-12-28 | MA    |    12
(1 row)

更新默认列值

通过从 date 中减去 30 天来更新表 t：

=> UPDATE t SET date = date-30;
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM t;
    date    | state | month
------------+-------+-------
 2017-11-28 | MA    |    12
(1 row)

month 中的值保持不变。

从列 date 刷新 month 中的默认值：

=> UPDATE t SET month=DEFAULT;
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM t;
    date    | state | month
------------+-------+-------
 2017-11-28 | MA    |    11
(1 row)

从用户定义的标量函数派生默认列值

本示例显示了添加了两个整数值的用户定义的标量函数。该函数名为 add2ints，采用两个实参。

开发和部署函数，如标量函数 (UDSF) 中所述。

创建示例表 t1，其中包含两个整数列：

=> CREATE TABLE t1 ( x int, y int );
CREATE TABLE

在 t1 中插入一些值：

=> insert into t1 values (1,2);
OUTPUT
--------
      1
(1 row)
=> insert into t1 values (3,4);
 OUTPUT
--------
      1
(1 row)

使用 ALTER TABLE 向 t1 中添加列，其默认列值派生自 UDSF add2ints：
```
alter table t1 add column z int default add2ints(x,y);
ALTER TABLE
```

列出新列：

select z from t1;
 z
----
  3
  7
(2 rows)

具有 SET USING 列的表，该列查询另一个表以获取其值

定义表 t1 和 t2。列 t2.b 被定义为通过其 SET USING 子句中的查询从列 t1.b 获取其数据：
```
=> CREATE TABLE t1 (a INT PRIMARY KEY ENABLED, b INT);
CREATE TABLE

=> CREATE TABLE t2 (a INT, alpha VARCHAR(10),
      b INT SET USING (SELECT t1.b FROM t1 WHERE t1.a=t2.a))
      ORDER BY a SEGMENTED BY HASH(a) ALL NODES;
CREATE TABLE
```
重要

表 t2 的定义包括排除 SET USING 列 b 的 SEGMENTED BY 和 ORDER BY 子句。如果省略这些子句，Vertica 会为此表创建一个自动投影，在其 SEGMENTED BY 和 ORDER BY 子句中指定列 b。在任何投影的分段或排序顺序中包含 SET USING 列会阻止 REFRESH_COLUMNS 函数填充此列。相反，它返回一个错误。

有关此限制和其他限制的详细信息，请参阅 REFRESH_COLUMNS。

使用数据填充表：

=> INSERT INTO t1 VALUES(1,11),(2,22),(3,33),(4,44);
=> INSERT INTO t2 VALUES (1,'aa'),(2,'bb');
=> COMMIT;
COMMIT

查看表 t2 中的数据：SET USING 列 b 中的列为空，等待调用 Vertica 函数 REFRESH_COLUMNS：
```
=> SELECT * FROM t2;
 a | alpha | b
---+-------+---
 1 | aa    |
 2 | bb    |
(2 rows)
```
通过调用 REFRESH_COLUMNS 函数刷新表 t2 中的列数据：
```
=> SELECT REFRESH_COLUMNS ('t2','b', 'REBUILD');
      REFRESH_COLUMNS
---------------------------
 refresh_columns completed
(1 row)
```
在此示例中，使用可选实参 REBUILD 调用 REFRESH_COLUMNS。此实参指定替换 SET USING 列 b 中的所有数据。对任何新的 SET USING 列使用 REBUILD 调用 REFRESH_COLUMNS 通常是一种很好的做法。有关详细信息，请参阅REFRESH_COLUMNS。
查看刷新后的列 b 中的数据，其数据是从表 t1 中获取的，如列的 SET USING 查询中指定的那样：
```
=> SELECT * FROM t2 ORDER BY a;
  a | alpha | b
---+-------+----
 1 | aa    | 11
 2 | bb    | 22
(2 rows)
```

具有相关子查询的表达式

DEFAULT 和 SET USING 表达式支持可以从其他表中获取值的子查询，并将这些子查询与当前表中的值结合使用来计算列值。以下示例将列 gmt_delivery_time 添加到事实表 customer_orders 中。该列指定一个 DEFAULT 表达式来设置新列中的值，如下所示：

调用元函数 NEW_TIME，它执行以下任务：
- 使用 customer_orders 中的客户密钥查询 customers 维度表以获得客户时区。
- 使用查询到的时区数据将本地交付时间转换为 GMT。
使用转换后的值填充 gmt_delivery_time 列。

=> CREATE TABLE public.customers(
    customer_key int,
    customer_name varchar(64),
    customer_address varchar(64),
    customer_tz varchar(5),
    ...);

=> CREATE TABLE public.customer_orders(
    customer_key int,
    order_number int,
    product_key int,
    product_version int,
    quantity_ordered int,
    store_key int,
    date_ordered date,
    date_shipped date,
    expected_delivery_date date,
    local_delivery_time timestamptz,
    ...);

=> ALTER TABLE customer_orders ADD COLUMN gmt_delivery_time timestamp
   DEFAULT NEW_TIME(customer_orders.local_delivery_time,
      (SELECT c.customer_tz FROM customers c WHERE (c.customer_key = customer_orders.customer_key)),
      'GMT');

管理表列

1 - 重命名列

2 - 更改列数据类型

支持的数据类型转换

不支持的数据类型转换

2.1 - 更改列宽

更改集合列

清除历史数据

2.2 - 使用列数据转换

添加一个新列供临时使用

删除原始 price 列

将新列重命名为原始列

3 - 定义列值

DEFAULT

注意

限制

SET USING

限制

DEFAULT USING

表达式限制

注意

消除谓词列的歧义

示例

从另一个列派生一个列的默认值

更新默认列值

从用户定义的标量函数派生默认列值

具有 SET USING 列的表，该列查询另一个表以获取其值

重要

具有相关子查询的表达式