定义列值

您可以定义一个列，以便 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 支持的所有运算符

• 以下函数类别：

  • NULL 处理

  • 用户定义的标量

  • 系统信息

  • 字符串

  • 数学

  • 格式

表达式限制

以下限制适用于 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)),
    ...
)

示例

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

1. 创建表 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)
   

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

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

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

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

更新默认列值

1. 通过从 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 中的值保持不变。

2. 从列 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，采用两个实参。

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

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

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

3. 在 t1 中插入一些值：

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

4. 使用 ALTER TABLE 向 t1 中添加列，其默认列值派生自 UDSF add2ints：

   alter table t1 add column z int default add2ints(x,y);
   ALTER TABLE
   

5. 列出新列：

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

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

1. 定义表 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。

2. 使用数据填充表：

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

3. 查看表 t2 中的数据：SET USING 列 b 中的列为空，等待调用 Vertica 函数 REFRESH_COLUMNS：

   => SELECT * FROM t2;
    a | alpha | b
   ---+-------+---
    1 | aa    |
    2 | bb    |
   (2 rows)
   

4. 通过调用 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。

5. 查看刷新后的列 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 表达式来设置新列中的值，如下所示：

1. 调用元函数 NEW_TIME，它执行以下任务：

   • 使用 customer_orders 中的客户密钥查询 customers 维度表以获得客户时区。

   • 使用查询到的时区数据将本地交付时间转换为 GMT。

2. 使用转换后的值填充 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');