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

返回本页常规视图.

数据类型

下表汇总了 Vertica 支持的内部数据类型。其中还显示了投影中 NULL 值的默认放置方式。“大小”列列出未压缩的字节数。

1 - 二进制数据类型（BINARY 和 VARBINARY）

存储原始字节数据，如 IP 地址，最多 65000 个字节。BINARY 和 BINARY VARYING (VARBINARY) 数据类型统称为二进制字符串类型，其值称为二进制字符串。二进制字符串是八位字节或字节序列。

BYTEA 和 RAW 是 VARBINARY 的同义词。

语法

BINARY ( length )

{ VARBINARY | BINARY VARYING | BYTEA | RAW } ( max-length )

实参

BINARY 和 VARBINARY 数据类型

BINARY 和 VARBINARY 数据类型具有以下属性：

  • BINARY：一个固定宽度的 length 字节字符串，其中字节数声明为类型的可选说明符。如果忽略 length，默认值为 1。必要时，值将向右扩展到包含零字节的列的全宽。例如：

    => SELECT TO_HEX('ab'::BINARY(4));
  to_hex
----------
 61620000

  • VARBINARY：一个宽度可变的字符串，可扩展到 max-length 字节长度，其中最大字节数声明为类型的可选说明符。默认值是默认的属性大小，即 80，最大长度为 65000 个字节。VARBINARY 值不会扩展到列的全宽。例如：

    => SELECT TO_HEX('ab'::VARBINARY(4));
 to_hex
--------
 6162

输入格式

使用二进制值时，可以使用多种格式。十六进制格式通常最简单，Vertica 文档中也曾强调过。

二进制值也可以通过在值前加上反斜杠 '\\' 以八进制格式表示。

也可以输入用可打印字符表示的值。例如，十六进制值 '0x61' 也可以用符号 a 来表示。

请参阅数据加载。

输入时，字符串将从

这两个函数都使用 VARCHAR 实参并返回 VARBINARY 值。

输出格式

和输入格式一样，输出格式是八进制代码和可打印的 ASCII 字符的组合。在可打印的 ASCII 字符范围（范围 [0x20, 0x7e]）内的字节由相应的 ASCII 字符表示，反斜杠 ('\\') 除外，反斜杠可以转义为 '\\\'。所有其他字节值由相应的八进制值表示。例如，字节 {97,92,98,99}，在 ASCII 中为 {a,\\,b,c}，转换成文本后为 'a\\\bc'。

二元运算符和函数

二元运算符 &、~、|和 # 针对二进制数据类型采取特殊的行为，如位运算符中所述。

二进制数据类型支持以下聚合函数：

BIT_AND、BIT_OR 和 BIT_XOR 是应用于组中每个非空值的位元运算，而 MAX 和 MIN 是二进制值的逐字节比较。

和对应的二元运算符一样，如果组中的值长度不同，聚合函数会通过用零字节将较短的值扩展到列的全宽，按照长度相等的值来处理这些值。例如，假定一个组包含值 'ff', null, and 'f'，二元聚合会忽略 null 值，并值 'f' 视为 'f0'。此外，和对应的二元运算符一样，这些聚合函数会显式处理 VARBINARY 类型并通过转换隐式处理 BINARY 类型。请参阅数据类型强制运算符 (CAST)。

二进制与字符数据类型

BINARY 和 VARBINARY 二进制类型分别类似于 CHAR 和 VARCHAR 字符数据类型。它们的区别如下：

  • 二进制数据类型包含字节串（八位字节或字节序列）。

  • 字符数据类型包含字符串（文本）。

  • 二进制数据类型的长度以字节为单位，而字符数据类型的长度以字符为单位。

示例

以下示例显示 HEX_TO_BINARY 和 TO_HEX 的用法。

表 t 及其投影用二进制列创建：

=> CREATE TABLE t (c BINARY(1));
=> CREATE PROJECTION t_p (c) AS SELECT c FROM t;

插入最小字节值和最大字节值：

=> INSERT INTO t values(HEX_TO_BINARY('0x00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFF'));

然后可以在输出时使用 TO_HEX 函数用十六进制设置二进制值的格式：

=> SELECT TO_HEX(c) FROM t;
 to_hex
 --------
  00
  ff
 (2 rows)

BIT_AND、BIT_OR 和 BIT_XOR 函数在处理一组值时很有用。例如，创建一个包含二进制列的示例表和投影：

示例使用 t 表，该表在单个列中包含了 VARBINARY 数据类型：

=> CREATE TABLE t ( c VARBINARY(2) );
=> INSERT INTO t values(HEX_TO_BINARY('0xFF00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFFFF'));
=> INSERT INTO t values(HEX_TO_BINARY('0xF00F'));

查询表 t 以查看列 c 输出：

=> SELECT TO_HEX(c) FROM t;
 TO_HEX
--------
 ff00
 ffff
 f00f
(3 rows)

现在发出位元 AND 运算。因为这些都是聚合函数，因此使用 (ff00&(ffff)&f00f) 对结果执行隐式 GROUP BY 操作：

=> SELECT TO_HEX(BIT_AND(c)) FROM t;
 TO_HEX
--------
f000
(1 row)

对 (ff00|(ffff)|f00f) 发出位元 OR 运算：

=> SELECT TO_HEX(BIT_OR(c)) FROM t;
 TO_HEX
--------
ffff
(1 row)

对 (ff00#(ffff)#f00f) 发出位元 XOR 运算：

=> SELECT TO_HEX(BIT_XOR(c)) FROM t;
 TO_HEX
--------
f0f0
(1 row)

2 - 布尔数据类型

Vertica 提供标准 SQL 类型 BOOLEAN，它有两种状态：true 与 false。SQL 布尔逻辑中的第三种状态是未知，由 NULL 值表示。

语法

BOOLEAN

参数

用于输入的有效字面量数据值如下：

注意

  • 不要将 BOOLEAN 数据类型与 布尔运算符 或 布尔谓词 相混淆。

  • 关键字 TRUE 和 FALSE 是首选项且兼容 SQL。

  • NULL 布尔值按升序显示在最后（最大）。

  • 所有其他值必须用单引号引起来。

  • 布尔值是使用字母 t 和 f 的输出。

另请参阅

3 - 字符数据类型（CHAR 和 VARCHAR）

存储由字母、数字和符号组成的字符串。数据类型 CHARACTER (CHAR) 和 CHARACTER VARYING (VARCHAR) 统称为字符串类型，而字符串类型的值称为字符串。

字符数据可以存储为固定长度或可变长度的字符串。固定长度的字符串在输出时通过空格向右扩展；可变长度的字符串不能扩展。

SQL 语句中的字符串字面量必须以单引号引起。

语法

{ CHAR | CHARACTER } [ (octet‑length) ]

{ VARCHAR | CHARACTER VARYING ] } [ (octet‑length) ]

实参

CHAR 与 VARCHAR 数据类型

以下差异适用于 CHAR 和 VARCHAR 数据：

  • CHAR 在概念上是固定长度、填充空格式字符串。结尾空格将在输入时移除，并且在输出时还原。默认长度为 1，最大长度为 65000 个八位字节（字节）。

  • VARCHAR 是可变长度的字符数据类型。默认长度为 80，最大长度为 65000 个八位字节。对于长于 65000 的字符串值，使用 长数据类型。值可以包括结尾空格。

通常，您对所有字符串数据使用 VARCHAR。在需要固定宽度的字符串输出时，请使用 CHAR。例如，对于要传输到需要固定宽度字符串的旧系统的数据，可以使用 CHAR 列。

设置最大长度

在您定义字符列时，请指定要存储在某一列中的任何字符串的最大大小。例如，要存储最大长度为 24 个八位字节的字符串，请使用以下定义之一：

CHAR(24)    --- fixed-length
VARCHAR(24) --- variable-length

VARCHAR 和 CHAR 数据类型的最大长度参数是指可存储在该字段中的八位字节数，而不是字符数（Unicode 代码点）。在使用多字节 UTF-8 字符时，必须根据数据将字段大小调整为每个字符能够容纳 1 至 4 个八位字节。如果加载到 VARCHAR 或 CHAR 列中的数据超过了为该列指定的最大大小，将根据 UTF-8 字符边界截断数据，以符合指定大小。请参阅 COPY。

由于 Vertica 中的压缩，导致过高估计这些字段的长度产生成本，主要是在加载时和排序期间产生成本。

NULL 与 NUL

NULL 和 NUL 区别如下：

  • NUL 表示其 ASCII/Unicode 代码为 0 的字符，有时限定为“ASCII NUL”。

  • NULL 是指没有值，字段（列）或常数皆是如此，字符却不。

CHAR、LONG VARCHAR 和 VARCHAR 字符串数据类型接受 ASCII NUL 值。

在升序排序中，NULL 出现在最后（最大）。

有关 NULL 排序的更多信息，请参阅 NULL 排序顺序。

以下示例将包含 NUL 值的输入字符串转换为 VARCHAR：

=> SELECT 'vert\0ica'::CHARACTER VARYING AS VARCHAR;
 VARCHAR
---------
 vert\0ica
(1 row)

结果包含 9 个字符：

=> SELECT LENGTH('vert\0ica'::CHARACTER VARYING);
 length
--------
      9
(1 row)

如果您使用扩展字符串字面量，则长度为 8 个字符：

=> SELECT E'vert\0ica'::CHARACTER VARYING AS VARCHAR;
 VARCHAR
---------
 vertica
(1 row)
=> SELECT LENGTH(E'vert\0ica'::CHARACTER VARYING);
 LENGTH
--------
      8
(1 row)

4 - 日期/时间数据类型

Vertica 支持全套 SQL 日期和时间数据类型。

以下规则适用于所有日期/时间数据类型：

  • 所有类型的大小均为 8 字节。

  • 相对于所有其他日期/时间值，日期/时间值 NULL 是最小的。

  • 基于一年的平均长度为 365.2425 天这一假设，Vertica 使用儒略日期进行所有日期/时间计算，可正确预测和计算任何日期，从早于 4713 BC 到很远的未来。

  • 所有的日期/时间数据类型均支持使用特殊字面量值 NOW 来指定当前日期和时间。例如：

    => SELECT TIMESTAMP 'NOW';
         ?column?
---------------------------
 2020-09-23 08:23:50.42325
(1 row)

  • 默认情况下，Vertica 舍入的最大精度为六位小数。可以用 0 到 6 之间的整数替换 p 以指定首选的精度级别。。

下表列出了日期/时间数据类型的特定属性：

输入的时区缩写

Vertica 将 /opt/vertica/share/timezonesets 中的文件识别为日期/时间输入值并定义 AT TIME ZONE zone 参数中接受的默认字符串列表。这些名称不一定用于日期/时间输出，输出使用当前所选时区参数设置相关的正式时区缩写。

4.1 - DATE

由月、日和年组成。

语法

DATE

参数/限制

有关排序的信息，请参阅 SET DATESTYLE。

4.2 - DATETIME

DATETIME 是 TIMESTAMP/TIMESTAMPTZ 的别名。

4.3 - INTERVAL

表示两个时间点之间的时间差。间隔可以是正，也可以是负。INTERVAL 数据类型符合 SQL:2008 标准，并支持拆分为两个主要子类型的间隔限定符：

  • 年-月：年和月的时间范围

  • 天-时间：以天、小时、分钟、秒以及毫秒为单位的时间跨度

间隔在内部表示为若干微秒，印刷为多达 60 秒钟、60 分钟、24 小时、30 天、12 个月，以及需要的年数。您可以使用 SET INTERVALSTYLE 和 SET DATESTYLE 来控制间隔单位的输出格式。

语法

INTERVAL 'interval-literal' [ interval-qualifier ] [ (p) ]

参数

限制

4.3.1 - 设置间隔单位显示

SET INTERVALSTYLE 和 SET DATESTYLE 控制间隔单位的输出格式。

省略间隔单位

要从输出中省略间隔单位，请将 INTERVALSTYLE 设置为 PLAIN。这是默认设置，符合 SQL:2008 标准：

=> SET INTERVALSTYLE TO PLAIN;
SET
=> SELECT INTERVAL '3 2';
?column?
----------
 3 02:00

当 INTERVALSTYLE 设置为 PLAIN 时，输出中会省略单位，即使查询指定了输入单位：

=> SELECT INTERVAL '3 days 2 hours';
 ?column?
----------
 3 02:00

如果 DATESTYLE 设置为 SQL，则 Vertica 符合 SQL:2008 标准，并始终从输出中省略间隔单位：


=> SET DATESTYLE TO SQL;
SET
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT INTERVAL '3 2';
 ?column?
----------
 3 02:00

显示间隔单位

要启用间隔单位的显示，必须将 DATESTYLE 设置为 ISO。然后，您可以通过将 INTERVALSTYLE 设置为 UNITS 来显示间隔单位：


=> SET DATESTYLE TO ISO;
SET
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT INTERVAL '3 2';
 ?column?
----------------
 3 days 2 hours

检查 INTERVALSTYLE 和 DATESTYLE 设置

使用 SHOW 语句检查 INTERVALSTYLE 和 DATESTYLE 设置：

=> SHOW INTERVALSTYLE;
     name      | setting
---------------+---------
 intervalstyle | units
=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | ISO, MDY

4.3.2 - 指定间隔输入

间隔值通过字面量类时间间隔表示。字面量类时间间隔由一个或多个间隔字段组成，其中每个字段代表天和时间或年和月的时间范围，如下所示：

[-] { quantity subtype-unit }[...] [AGO]

使用子类型单位

子类型单位对于天-时间间隔是可选的；而对于年-月间隔则必须指定。

例如，下面的第一个语句隐式指定了天和时间；第二个语句显式标识天和时间单位。两个语句都返回相同的结果：


=> SET INTERVALSTYLE TO UNITS;
=> SELECT INTERVAL '1 12:59:10:05';
      ?column?
--------------------
 1 day 12:59:10.005
(1 row)

=> SELECT INTERVAL '1 day 12 hours 59 min 10 sec 5 milliseconds';
      ?column?
--------------------
 1 day 12:59:10.005
(1 row)

以下两个语句分别将 28 天和 4 周添加到当前日期。两种情况下的间隔相等，并且语句返回相同的结果。但是，在第一个语句中，字面量类时间间隔省略了子类型（隐式 days）；在第二个语句中，字面量类时间间隔必须包含子类型单位 weeks：

=> SELECT CURRENT_DATE;
  ?column?
------------
 2016-08-15
(1 row)

=> SELECT CURRENT_DATE + INTERVAL '28';
      ?column?
---------------------
 2016-09-12 00:00:00
(1 row)

dbadmin=> SELECT CURRENT_DATE + INTERVAL '4 weeks';
      ?column?
---------------------
 2016-09-12 00:00:00
(1 row)

字面量类时间间隔可以包括天-时间和年-月字段。例如，以下语句将 4 年、4 周、4 天和 14 小时的间隔添加到当前日期。年和周字段必须包含子类型单位；天和小时字段则将其省略：

> SELECT CURRENT_DATE + INTERVAL '4 years 4 weeks 4 14';
      ?column?
---------------------
 2020-09-15 14:00:00
(1 row)

省略子类型单位

您可以指定天、小时、分钟和秒的数量，无需指定单位。Vertica 将字面量类时间间隔中的冒号识别为时间戳的一部分：

=> SELECT INTERVAL '1 4 5 6';
 ?column?
------------
 1 day 04:05:06
=> SELECT INTERVAL '1 4:5:6';
 ?column?
------------
 1 day 04:05:06
=> SELECT INTERVAL '1 day 4 hour 5 min 6 sec';
 ?column?
------------
 1 day 04:05:06

如果 Vertica 无法确定单位，它会根据间隔限定符将该数量应用于缺失单位。在接下来的两个示例中，Vertica 使用默认间隔限定符 (DAY TO SECOND(6)) 并将尾数 1 分配给天，因为它已经在输出中处理了小时、分钟和秒：

=> SELECT INTERVAL '4:5:6 1';
 ?column?
------------
 1 day 04:05:06
=> SELECT INTERVAL '1 4:5:6';
 ?column?
------------
 1 day 04:05:06

在接下来的两个示例中，Vertica 将 4:5 识别为 hours:minutes。将字面量类时间间隔中的剩余值分配给缺失单位： 1 分配给天，而 2 分配给秒。

SELECT INTERVAL '4:5 1 2';
  ?column?
------------
 1 day 04:05:02
=> SELECT INTERVAL '1 4:5 2';
 ?column?
------------
 1 day 04:05:02

指定间隔限定符可以改变 Vertica 解释 4:5 的方式：

=> SELECT INTERVAL '4:5' MINUTE TO SECOND;
  ?column?
------------
 00:04:05

4.3.3 - 控制间隔格式

间隔限定符指定 Vertica 用来解释和格式化字面量类时间间隔的一系列选项。间隔限定符也可以指定精度。每个间隔限定符由一个或两个单位组成：

unit[p] [ TO unit[p] ]

其中：

  • 单位指定天-时间或年-月子类型。

  • p 指定精度，0 到 6 之间的整数。通常，精度仅适用于 SECOND 单位。SECOND 的默认精度为 6。有关详细信息，请参阅指定时间间隔精度。

如果间隔省略了间隔限定符，Vertica 将使用默认值 DAY TO SECOND(6)。

间隔限定符类别

间隔限定符属于以下类别之一：

  • 年-月：年和月的时间范围

  • 天-时间：以天、小时、分钟、秒以及毫秒为单位的时间跨度

年-月

Vertica 支持两种年-月子类型： YEAR 和 MONTH。

在以下示例中，YEAR TO MONTH 限定字面量类时间间隔 1 2 以指示时间范围为 1 年零两个月：

=> SELECT interval '1 2' YEAR TO MONTH;
 ?column?
----------
 1-2
(1 row)

如果省略限定符，Vertica 将使用默认间隔限定符 DAY TO SECOND 并返回不同的结果：

=> SELECT interval '1 2';
 ?column?
----------
 1 02:00
(1 row)

以下示例使用间隔限定符 YEAR。在这种情况下，Vertica 仅从字面量类时间间隔 1y 10m 中提取年份：

=> SELECT INTERVAL '1y 10m' YEAR;
 ?column?
----------
 1
(1 row)

在下一个示例中，间隔限定符 MONTH 将相同的字面量类时间间隔转换为月份：

=> SELECT INTERVAL '1y 10m' MONTH;
 ?column?
----------
 22
(1 row)

天-时间

Vertica 支持四种天-时间子类型：DAY、HOUR、MINUTE 和 SECOND。

在以下示例中，间隔限定符 DAY TO SECOND(4) 限定字面量类时间间隔 1h 3m 6s 5msecs 57us。限定符还将秒的精度设置为 4：

=> SELECT INTERVAL '1h 3m 6s 5msecs 57us' DAY TO SECOND(4);
   ?column?
---------------
 01:03:06.0051
(1 row)

如果未指定间隔限定符，则 Vertica 将使用默认子类型 DAY TO SECOND(6)，无论您如何指定字面量类时间间隔。例如，作为 SQL:2008 的扩展，以下两个命令都将返回 91days：

=> SELECT INTERVAL '2-6';
    ?column?
-----------------
 910
=> SELECT INTERVAL '2 years 6 months';
 ?column?
-----------------
 910

间隔限定符可以从输入参数中提取其他值。例如，以下命令从字面量类时间间隔 3 days 2 hours 中提取 HOUR 值：

=> SELECT INTERVAL '3 days 2 hours' HOUR;
 ?column?
----------
 74

主要的天/时间 (DAY TO SECOND) 和年/月 (YEAR TO MONTH) 子类型范围可以通过间隔限定符限制为更具体的类型范围。例如，HOUR TO MINUTE 是天/时间间隔的有限形式，可用于表示时区偏移量。

=> SELECT INTERVAL '1 3' HOUR to MINUTE;
 ?column?
---------------
 01:03

hh:mm:ss 和 hh:mm 格式仅在间隔限定符中指定的至少两个字段非零且不超过 23 小时或 59 分钟时使用：

=> SELECT INTERVAL '2 days 12 hours 15 mins' DAY TO MINUTE;
  ?column?
--------------
 2 12:15
=> SELECT INTERVAL '15 mins 20 sec' MINUTE TO SECOND;
 ?column?
----------
 15:20
=> SELECT INTERVAL '1 hour 15 mins 20 sec' MINUTE TO SECOND;
 ?column?
-----------------
 75:20

4.3.4 - 指定时间间隔精度

通常，时间间隔精度仅适用于秒。如果未明确指定精度，则 Vertica 将精度四舍五入到最多六位小数。例如：

=> SELECT INTERVAL '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND;
    ?column?
-----------------
 02:04:03.709385
(1 row)

Vertica 允许您通过两种方式指定时间间隔精度：

  • 在 INTERVAL 关键字之后

  • 在时间间隔限定符的 SECOND 单元之后，指定以下之一：

    • DAY TO SECOND

    • HOUR TO SECOND

    • MINUTE TO SECOND

    • SECOND

例如，以下语句使用全部两种方法来设置精度，并返回相同的结果：

=> SELECT INTERVAL(4) '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND;
   ?column?
---------------
 02:04:03.7094
(1 row)

=> SELECT INTERVAL '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND(4);
   ?column?
---------------
 02:04:03.7094
(1 row)

如果同一语句多次指定精度，则 Vertica 使用较低的精度。例如，以下语句指定两次精度：INTERVAL 关键字指定精度 1，时间间隔限定符 SECOND 指定精度 2。Vertica 使用较低的精度 1：

=> SELECT INTERVAL(1) '1.2467' SECOND(2);
 ?column?
----------
 1.2 secs

设置时间间隔表列的精度

如果创建具有时间间隔列的表，则以下限制适用于列定义：

  • 仅当您不指定时间间隔限定符时，才能在 INTERVAL 关键字上设置精度。如果您尝试在 INTERVAL 关键字上设置精度并包含时间间隔限定符，则 Vertica 返回错误。

  • 您只能在时间间隔限定符的最后一个单元上设置精度。例如：

    CREATE TABLE public.testint2
(
    i INTERVAL HOUR TO SECOND(3)
);

    如果您在另一个单元上指定精度，Vertica 会在保存表定义时将其丢弃。

4.3.5 - 间隔单位的小数秒

Vertica 支持以毫秒为单位的时间间隔 (hh:mm:ss:ms)，其中 01:02:03:25 表示 1 小时、2 分钟、3 秒和 025 毫秒。毫秒转换为小数秒，如下例所示，返回 1 天、2 小时、3 分钟、4 秒和 25.5 毫秒：

=> SELECT INTERVAL '1 02:03:04:25.5';
  ?column?
------------
 1 day 02:03:04.0255

Vertica 允许使用小数分钟。小数分钟四舍五入为秒：

=> SELECT INTERVAL '10.5 minutes';
  ?column?
------------
 00:10:30
=> select interval '10.659 minutes';
  ?column?
-------------
 00:10:39.54
=> select interval '10.3333333333333 minutes';
 ?column?
----------
 00:10:20

考量

  • INTERVAL 只能包含您需要的单位子集；但是，年/月间隔表示没有固定天数的日历年和月，因此年/月间隔值不能包括天、小时、分钟。当为天/时间间隔指定年/月值时，间隔扩展假定每月 30 天，每年 365 天。由于给定月份或年份的长度会有所不同，因此天/时间间隔永远不会以月或年的形式输出，而只会以天、小时、分钟等形式输出。

  • 天/时间和年/月间隔在逻辑上是相互独立的，不能相互组合或相互比较。在以下示例中，包含 DAYS 的字面量类时间间隔不能与 YEAR TO MONTH 类型组合：

    => SELECT INTERVAL '1 2 3' YEAR TO MONTH;
ERROR 3679:  Invalid input syntax for interval year to month: "1 2 3"

  • Vertica 接受最多 2^63 – 1 微秒或月（约 18 位）的时间间隔。

  • INTERVAL YEAR TO MONTH 可在分析 RANGE 窗口中使用，其条件是当 ORDER BY 列类型为 TIMESTAMP/TIMESTAMP WITH TIMEZONE 或 DATE 时。不支持使用 TIME/TIME WITH TIMEZONE。

  • 当 ORDER BY 列类型为 TIMESTAMP/TIMESTAMP WITH TIMEZONE、DATE 和 TIME/TIME WITH TIMEZONE 时，您可以使用 INTERVAL DAY TO SECOND。

示例

此部分中的示例假设 INTERVALSTYLE 设置为 PLAIN，因此结果省略了子类型单位。省略间隔限定符的间隔值使用默认值 DAY TO SECOND(6)。

4.3.6 - 处理带符号的间隔

在 SQL:2008 标准中，字面量类时间间隔前的减号或作为字面量类时间间隔的第一个字符的减号会整个字面量为负，而不仅仅是第一个组件。在 Vertica 中，前导减号会使整个间隔为负，而不仅仅是第一个组件。以下命令都返回相同的值：

=> SELECT INTERVAL '-1 month - 1 second';
 ?column?
----------
 -29 days 23:59:59

=> SELECT INTERVAL -'1 month - 1 second';
 ?column?
----------
 -29 days 23:59:59

改为使用以下命令之一来返回预期结果：

=> SELECT INTERVAL -'1 month 1 second';
 ?column?
----------
 -30 days 1 sec
=> SELECT INTERVAL -'30 00:00:01';
 ?column?
----------
 -30 days 1 sec

两个负号叠加会返回一个正数：

=> SELECT INTERVAL -'-1 month - 1 second';
 ?column?
----------
 29 days 23:59:59
=> SELECT INTERVAL -'-1 month 1 second';
 ?column?
----------
 30 days 1 sec

您可以使用不带空格的年-月语法。Vertica 允许输入负数月份，但与年份配对时需要两个负数。

=> SELECT INTERVAL '3-3' YEAR TO MONTH;
 ?column?
----------
 3 years 3 months
=> SELECT INTERVAL '3--3' YEAR TO MONTH;
 ?column?
----------
 2 years 9 months

当字面量类时间间隔看起来像年/月类型，但类型是天/秒（反之亦然）时，Vertica 会从左到右读取字面量类时间间隔，而数字-数字是年-月，数字 <空格> <带符号的数字> 是指定的任何单位。Vertica 将以下命令处理为 (–) 1 年 1 个月 = (–) 365 + 30 = –395 天：

=> SELECT INTERVAL '-1-1' DAY TO HOUR;
 ?column?
----------
 -395 days

如果您在字面量类时间间隔中插入空格，Vertica 会根据子类型 DAY TO HOUR 对其进行处理：(–) 1 天 – 1 小时 = (–) 24 – 1 = –23 小时：

=> SELECT INTERVAL '-1 -1' DAY TO HOUR;
 ?column?
----------
 -23 hours

两个负号叠加会返回一个正数，因此 Vertica 将以下命令处理为 (–) 1 年 – 1 个月 = (–) 365 – 30 = –335 天：

=> SELECT INTERVAL '-1--1' DAY TO HOUR;
  ?column?
----------
 -335 days

如果省略连字符后的值，则 Vertica 假定 0 个月并将以下命令处理为 1 年 0 月 –1 天 = 365 + 0 – 1 = –364 天：

=> SELECT INTERVAL '1- -1' DAY TO HOUR;
  ?column?
----------
 364 days

4.3.7 - 间隔转换

您可以使用 CAST 将字符串转换为间隔，反之亦然。

字符串到间隔

您可以如下所示将字符串转换为间隔：

CAST( [ INTERVAL[(p)] ] [-] ] interval‑literal AS INTERVAL[(p)] interval‑qualifier )

例如：

=> SELECT CAST('3700 sec' AS INTERVAL);
 ?column?
----------
 01:01:40

您可以在日期-时间或年-月子类型中投射转换间隔，但不能在其之间转换：

=> SELECT CAST(INTERVAL '4440' MINUTE as INTERVAL);
  ?column?
----------
 3 days 2 hours
=> SELECT CAST(INTERVAL -'01:15' as INTERVAL MINUTE);
 ?column?
----------
 -75 mins

间隔到字符串

您可以如下所示将间隔转换为字符串：

CAST( (SELECT interval ) AS VARCHAR[(n)] )

例如：

=> SELECT CONCAT(
  'Tomorrow at this time: ',
  CAST((SELECT INTERVAL '24 hours') + CURRENT_TIMESTAMP(0) AS VARCHAR));
                    CONCAT
-----------------------------------------------
 Tomorrow at this time: 2016-08-17 08:41:23-04
(1 row)

4.3.8 - 间隔操作

如果用间隔除以间隔，您可以获得一个 FLOAT：

=> SELECT INTERVAL '28 days 3 hours' HOUR(4) / INTERVAL '27 days 3 hours' HOUR(4);
 ?column?
------------
 1.036866359447

FLOAT 除以 INTERVAL 会返回 INTERVAL：

=> SELECT INTERVAL '3' MINUTE / 1.5;
 ?column?
------------
 2 mins

INTERVAL MODULO （余数）INTERVAL 返回 INTERVAL：

=> SELECT INTERVAL '28 days 3 hours' HOUR % INTERVAL '27 days 3 hours' HOUR;
  ?column?
------------
 24 hours

如果添加 INTERVAL 和 TIME，则结果为 TIME，取模 24 小时：

=> SELECT INTERVAL '1' HOUR + TIME '1:30';
 ?column?
------------
 02:30:00

4.4 - SMALLDATETIME

SMALLDATETIME 是 TIMESTAMP/TIMESTAMPTZ 的别名。

4.5 - TIME/TIMETZ

存储一天中的指定时间。 TIMETZ 与 TIME WITH TIME ZONE 相同：两种数据类型都存储指定时间的 UTC 偏移量。

语法

TIME [ (p) ] [ { WITHOUT | WITH } TIME ZONE ] 'input‑string' [ AT TIME ZONE zone ]

参数

TIME 与 TIMETZ

TIMETZ 和 TIMESTAMPTZ 不是并行的 SQL 结构。 TIMESTAMPTZ 会记录从 转换的 GMT 时间和日期。TIME ZONE.TIMETZ 会以分钟数记录指定的时间和指定 GMT 时区。

限制

输入字符串

TIME 输入字符串可以设置为如下所示的任意格式：

数据类型强制转换

您可以将 TIME 或 TIMETZ 间隔转换为 TIMESTAMP。这将返回本地日期和时间，如下所示：

=> SELECT (TIME '3:01am')::TIMESTAMP;
       ?column?
---------------------
 2012-08-30 03:01:00
(1 row)

=> SELECT (TIMETZ '3:01am')::TIMESTAMP;
      ?column?
---------------------
 2012-08-22 03:01:00
(1 row)

将相同的 TIME 或 TIMETZ 间隔转换为 TIMESTAMPTZ，返回带 UTC 偏移量的本地日期和时间（在本例中为 -05）：

=> SELECT (TIME '3:01am')::TIMESTAMPTZ;
        ?column?
------------------------
 2016-12-08 03:01:00-05
(1 row)

4.6 - TIME AT TIME ZONE

将指定的 TIME 转换为另一个时区的时间。

语法

TIME [WITH TIME ZONE] 'input‑string' AT TIME ZONE 'zone'

参数

您可以通过两种方式指定时区：

  • 字符串字面量，例如 America/Chicago 或 PST

  • 指定 UTC 偏移量的时间间隔 — 例如， INTERVAL '‑08:00'

使用指示地理位置的字面量来指定时区通常是一种很好的做法。Vertica 会进行必要的季节性调整，从而避免结果不一致。例如，以下两个查询在夏令时生效时发出。因为白天的本地 UTC 偏移量是 ‑04，所以两个查询返回相同的结果：

=> SELECT CURRENT_TIME(0) "EDT";
     EDT
-------------
 12:34:35-04
(1 row)

=> SELECT CURRENT_TIME(0) AT TIME ZONE 'America/Denver' "Mountain Time";
 Mountain Time
---------------
 10:34:35-06
(1 row)
=> SELECT CURRENT_TIME(0) AT TIME ZONE INTERVAL '-06:00' "Mountain Time";
 Mountain Time
---------------
 10:34:35-06
(1 row)

如果您在标准时间生效时在类似查询中发出使用 UTC 偏移量，则必须相应调整 UTC 偏移量（对于丹佛时间，调整为 ‑07），否则 Vertica 会返回不同的（并且错误）结果：

=> SELECT CURRENT_TIME(0) "EST";
     EST
-------------
 14:18:22-05
(1 row)

=> SELECT CURRENT_TIME(0) AT TIME ZONE INTERVAL '-06:00' "Mountain Time";
 Mountain Time
---------------
 13:18:22-06
(1 row)

您可以分别使用 SHOW TIMEZONE 和 SET TIME ZONE 显示和设置会话的时区：

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

=> SELECT CURRENT_TIME(0) "Eastern Daylight Time";
 Eastern Daylight Time
-----------------------
 12:18:24-04
(1 row)

=> SET TIMEZONE 'America/Los_Angeles';
SET

=> SELECT CURRENT_TIME(0) "Pacific Daylight Time";
 Pacific Daylight Time
-----------------------
 09:18:24-07
(1 row)

要查看默认的有效字面量列表，请参阅以下目录中的文件：

opt/vertica/share/timezonesets

例如：

$ cat Antarctica.txt
...
# src/timezone/tznames/Antarctica.txt
#

AWST    28800    # Australian Western Standard Time
                 #     (Antarctica/Casey)
                 #     (Australia/Perth)
...

NZST    43200    # New Zealand Standard Time
                 #     (Antarctica/McMurdo)
                 #     (Pacific/Auckland)
ROTT   -10800    # Rothera Time
                 #     (Antarctica/Rothera)
SYOT    10800    # Syowa Time
                 #     (Antarctica/Syowa)
VOST    21600    # Vostok time
                 #     (Antarctica/Vostok)

示例

以下示例假定本地时间为 EST（东部标准时间）。查询将指定时间转换为 MST（山地标准时间）：

=> SELECT CURRENT_TIME(0);
  timezone
-------------
 10:10:56-05
(1 row)

=> SELECT TIME '10:10:56' AT TIME ZONE 'America/Denver' "Denver Time";
 Denver Time
-------------
 08:10:56-07
(1 row)

下一个示例将时区字面量添加到输入字符串（在本例中为 Europe/Vilnius）并将时间转换为 MST：

=> SELECT TIME '09:56:13 Europe/Vilnius' AT TIME ZONE 'America/Denver';
 Denver Time
-------------
 00:56:13-07
(1 row)

另请参阅

4.7 - TIMESTAMP/TIMESTAMPTZ

存储指定的日期和时间。 TIMESTAMPTZ 与 TIMESTAMP WITH TIME ZONE 相同：两种数据类型都存储指定时间的 UTC 偏移量。

TIMESTAMP 是 DATETIME 和 SMALLDATETIME 的别名。

语法

TIMESTAMP [ (p) ] [ { WITHOUT | WITH } TIME ZONE ] 'input‑string' [AT TIME ZONE zone ]

TIMESTAMPTZ [ (p) ] 'input‑string' [ AT TIME ZONE zone ]

参数

限制

在下表中，所有值均为约略值。有关详细信息，请参阅日期/时间数据类型。

输入字符串

日期/时间输入字符串连接日期和时间。输入字符串可以包含一个时区，指定为诸如 America/Chicago 之类的字面量，或指定为 UTC 偏移量。

以下列表表示典型的日期/时间输入变体：

  • 1999-01-08 04:05:06

  • 1999-01-08 04:05:06 -8:00

  • January 8 04:05:06 1999 PST

输入字符串还可以指定日历年代，AD（默认）或 BC。如果您省略日历年代，则 Vertica 会假定当前日历年代 (AD)。日历年代通常遵循时区；但是，输入字符串可以将其包含在不同位置。例如，以下查询返回相同结果：

=> SELECT TIMESTAMP WITH TIME ZONE 'March 1, 44 12:00 CET BC ' "Caesar's Time of Death EST";
 Caesar's Time of Death EST
----------------------------
 0044-03-01 06:00:00-05 BC
(1 row)

=> SELECT TIMESTAMP WITH TIME ZONE 'March 1, 44 12:00 BC CET' "Caesar's Time of Death EST";
 Caesar's Time of Death EST
----------------------------
 0044-03-01 06:00:00-05 BC
(1 row)

示例

4.8 - TIMESTAMP AT TIME ZONE

将指定的 TIMESTAMP 或 TIMESTAMPTZ (TIMESTAMP WITH TIMEZONE) 转换为另一个时区。Vertica 以不同方式执行 AT TIME ZONE，具体取决于日期输入是 TIMESTAMP 还是 TIMESTAMPTZ。请参阅下面的 TIMESTAMP 与 TIMESTAMPTZ 行为。

语法

timestamp‑clause AT TIME ZONE 'zone'

参数

TIMESTAMP 与 TIMESTAMPTZ 行为

Vertica 如何解释 AT TIME ZONE 取决于日期输入是 TIMESTAMP 还是 TIMESTAMPTZ：

您可以通过两种方式指定时区：

  • 字符串字面量，例如 America/Chicago 或 PST

  • 指定 UTC 偏移量的时间间隔 — 例如， INTERVAL '‑08:00'

使用指示地理位置的字面量来指定时区通常是一种很好的做法。Vertica 会进行必要的季节性调整，从而避免结果不一致。例如，以下两个查询在夏令时生效时发出。因为白天的本地 UTC 偏移量是 ‑04，所以两个查询返回相同的结果：

=> SELECT TIMESTAMPTZ '2017-03-16 09:56:13' AT TIME ZONE 'America/Denver' "Denver Time";
     Denver Time
---------------------
 2017-03-16 07:56:13
(1 row)

=> SELECT TIMESTAMPTZ '2017-03-16 09:56:13' AT TIME ZONE INTERVAL '-06:00' "Denver Time";
     Denver Time
---------------------
 2017-03-16 07:56:13
(1 row)

如果您在标准时间生效时在类似查询中发出使用 UTC 偏移量，则必须相应调整 UTC 偏移量（对于丹佛时间，调整为 ‑07），否则 Vertica 会返回不同的（并且错误）结果：

=> SELECT TIMESTAMPTZ '2017-01-16 09:56:13' AT TIME ZONE 'America/Denver' "Denver Time";
     Denver Time
---------------------
 2017-0-16 07:56:13
(1 row)

=> SELECT TIMESTAMPTZ '2017-01-16 09:56:13' AT TIME ZONE INTERVAL '-06:00' "Denver Time";
     Denver Time
---------------------
 2017-01-16 08:56:13
(1 row)

您可以分别使用 SHOW TIMEZONE 和 SET TIME ZONE 显示和设置会话的时区：

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

=> SELECT CURRENT_TIMESTAMP(0) "Eastern Daylight Time";
 Eastern Daylight Time
------------------------
 2017-03-20 12:18:24-04
(1 row)

=> SET TIMEZONE 'America/Los_Angeles';
SET

=> SELECT CURRENT_TIMESTAMP(0) "Pacific Daylight Time";
 Pacific Daylight Time
------------------------
 2017-03-20 09:18:24-07
(1 row)

要查看默认的有效字面量列表，请参阅以下目录中的文件：

opt/vertica/share/timezonesets

例如：

$ cat Antarctica.txt
...
# src/timezone/tznames/Antarctica.txt
#

AWST    28800    # Australian Western Standard Time
                 #     (Antarctica/Casey)
                 #     (Australia/Perth)
...

NZST    43200    # New Zealand Standard Time
                 #     (Antarctica/McMurdo)
                 #     (Pacific/Auckland)
ROTT   -10800    # Rothera Time
                 #     (Antarctica/Rothera)
SYOT    10800    # Syowa Time
                 #     (Antarctica/Syowa)
VOST    21600    # Vostok time
                 #     (Antarctica/Vostok)

另请参阅

5 - 长数据类型

存储数据高达 32000000 八位字节。Vertica 支持两种长数据类型：

  • LONG VARBINARY：可变长度的原始字节数据，如空间数据。 LONG VARBINARY 值不会扩展到整个列宽。

  • LONG VARCHAR：可变长度的字符串，如日志文件和非结构化数据。 LONG VARCHAR 值不会扩展到整个列宽。

仅当需要存储的数据超过 VARBINARY 和 VARCHAR 数据类型的最大大小 (65 KB) 时使用 LONG 数据类型。长数据可能包含非结构化数据、在线评论或邮件，或小的日志文件。

Flex 表具有默认的 LONG VARBINARY __raw__ 列，该列具有 NOT NULL 限制。有关详细信息，请参阅Flex 表。

语法

LONG VARBINARY [(max‑length)]

LONG VARCHAR [(octet‑length)]

参数

优化性能

为了 LONG 数据类型的最佳性能，Vertica 推荐您：

  • 将 LONG 数据类型用作仅存储容器；Vertica 支持对 LONG 数据类型的内容进行操作，但不支持 VARCHAR 和 VARBINARY 进行的所有操作。

  • 在可能情况下，使用 VARBINARY 和 VARCHAR 数据类型代替 LONG。 VARBINARY 和 VARCHAR 数据类型更灵活，且具有更广泛的操作。

  • 不要在 LONG 数据类型列上对投射进行排序、分段或分区。

  • 不要在任何 LONG VARBINARY 或 LONG VARCHAR 列上添加限制，例如主键。

  • 不要结合或聚类任何 LONG 数据类型列。

示例

下面的例子创建一个具有 user_comments 列的表 LONG VARCHAR，并在其中插入数据：

=> CREATE TABLE user_comments
                  (id INTEGER,
                  username VARCHAR(200),
                  time_posted TIMESTAMP,
                  comment_text LONG VARCHAR(200000));
=> INSERT INTO user_comments VALUES
                  (1,
                  'User1',
                  TIMESTAMP '2013-06-25 12:47:32.62',
                  'The weather tomorrow will be cold and rainy and then
                  on the day after, the sun will come and the temperature
                  will rise dramatically.');

6 - 数字数据类型

数字数据类型是存储在数据库列中的数字。这些数据类型通常按照下列方式分类：

  • 精确 数字类型，需要保存值的精度和标度。精确数字类型有 INTEGER、BIGINT、DECIMAL、NUMERIC、NUMBER 和 MONEY。

  • 近似 数字类型，需要保存值的精度，标度可浮动。近似数字类型有 DOUBLE PRECISION、FLOAT 和 REAL。

不支持从 INTEGER、FLOAT 和 NUMERIC 到 VARCHAR 的隐式转换。如果您需要该功能，可使用下列形式之一进行显式转换：

CAST(numeric-expression AS data-type)

numeric-expression::data-type

例如，您可以将浮点数转换为整数，如下所示：

=> SELECT(FLOAT '123.5')::INT;
 ?column?
----------
      124
(1 row)

字符串到数字的数据类型转换针对科学记数法、二进制记数法、十六进制和数字型字面量组合，接受带引号常数的格式：

  • 科学记数法:

    => SELECT FLOAT '1e10';
  ?column?
-------------
 10000000000
(1 row)

  • BINARY 缩放:

    => SELECT NUMERIC '1p10';
 ?column?
----------
     1024
(1 row)

  • 十六进制：

    => SELECT NUMERIC '0x0abc';
 ?column?
----------
     2748
(1 row)

6.1 - DOUBLE PRECISION (FLOAT)

Vertica 支持数字数据类型 DOUBLE PRECISION，此为 IEEE-754 8 位浮点类型，适用于大多数常规浮点运算。

语法

[ DOUBLE PRECISION | FLOAT | FLOAT(n) | FLOAT8 | REAL ]

参数

双精度是一种非精确、精度可变的数字类型。换言之，某些值无法精确表示，而是存储为近似值。因此，涉及双精度的输入和输出操作可能会有些许偏差。

  • 所有 DOUBLE PRECISION 数据类型都等同于 64 位 IEEE 浮点型。

  • FLOAT(n) 中的 n 必须介于 1 和 53 之间（包含），但始终使用 53 位小数。有关详细信息，请参阅 IEEE-754 标准。

  • 要进行精确的数字存储和计算（例如对于金钱），请使用 NUMERIC。

  • 浮点计算受限于底层处理器、操作系统和编译器的行为。

  • 比较两个浮点值是否相等，结果可能与预期不符。

  • 虽然 Vertica 在内部将十进制值视为 FLOAT，但如果将列定义为 FLOAT，则您无法从 ORC 和 Parquet 文件中读取十进制值。在这些格式中，FLOAT 和 DECIMAL 是不同的类型。

值

接受以下格式的浮点数据：

  • 前置空格（可选）

  • 加号 ("+") 或减号 ("-")（可选）

  • 十进制数、十六进制数、无穷大、NAN 或 NULL 值。

十进制数

十进制数由一组非空十进制数字序列组成，可能包含基点字符（小数点 "."），其后可跟十进制指数（可选）。十进制指数表示乘以 10 的若干次幂，其构成为：字母 E 或 e，后跟加号或减号（可选），之后跟一组非空十进制数字序列。

十六进制数

十六进制数由 "0x" 或 "0X" 后跟一组非空十六进制数字序列构成，可能包含基点字符，其后可跟二进制指数（可选）。二进制指数表示乘以 2 的若干次幂，其构成为：字母 "P" 或 "p" ，后跟加号或减号（可选），之后跟一组非空十进制数字序列。基点字符和二进制指数这两者必须至少出现一个。

无穷大

无穷大为 INF 或 INFINITY，表示忽略的情况。

NaN（非数值）

NaN 为 NAN（忽略的情况），其后可跟括在括号内的字符序列（可选）。字符串以实现相关的方式指定 NaN 的值。（在 x86 机器上，NAN 在 Vertica 内部表示为 0xfff8000000000000LL。）

将无穷大或 NAN 值作为常数写入 SQL 语句时，需加单引号。例如：

=> UPDATE table SET x = 'Infinity'

后面提供了 NaN 的一个示例。

=> SELECT CBRT('Nan'); -- cube root
 CBRT 
------
 NaN
(1 row)

=> SELECT 'Nan' > 1.0;
 ?column?
----------
 f
(1 row)

Null 值

Null 值的加载文件格式是用户定义的，如 COPY 命令中所述。Null 值在 Vertica 内部表示为 0x7fffffffffffffffLL。交互格式由 vsql 打印选项 null 控制。例如：

\pset null '(null)'

默认选项为不打印任何内容。

规则

  • -0 == +0

  • 1/0 = 无穷大

  • 0/0 == Nan

  • NaN != 任意值（甚至 NaN）

要搜索 NaN 列值，请使用以下谓词：

... WHERE column != column

这是必要的，因为根据定义，WHERE *column *= 'Nan' 不能为 true。

排序顺序（升序）

  • NAN

  • -Inf

  • 数字

  • +Inf

  • NULL

注意

  • NULL 按照升序在最后显示（最大值）。

  • 按照 IEEE 浮点标准，浮点数中的所有溢出均会生成 +/- 无穷大或 NaN。

6.2 - INTEGER

有符号的 8 字节 (64 位) 数据类型。

语法

[ INTEGER | INT | BIGINT | INT8 | SMALLINT | TINYINT ]

参数

INT、INTEGER、INT8、SMALLINT、TINYINT 以及 BIGINT 是针对同一种有符号 64 位整数数据类型的所有同义词。当不需要完整的 64 位时，可采用自动压缩技术来节省磁盘空间。

注意

  • 值的范围为 –2^63+1 到 2^63-1。

  • 2^63 = 9,223,372,036,854,775,808 (19 位数)。

  • –2^63 被保留，表示 NULL。

  • NULL 按升序最先显示（最小）。

  • Vertica 不具有显式 4 字节（32 位整数）或较小类型。Vertica 编码及自动压缩可以降低不足 64 位值的存储开销。

限制

  • JDBC 型 INTEGER 有 4 个字节，不受 Vertica 的支持。请改为使用 BIGINT。

  • Vertica 不支持 SQL/JDBC 型 NUMERIC、SMALLINT 或 TINYINT。

  • Vertica 不检查溢出（正或负），聚合函数 SUM() 除外。如果在使用 SUM 时遇到溢出问题，请使用 SUM_FLOAT() 将其转换为浮点。

另请参阅

数据类型强制转换表

6.3 - NUMERIC

数字数据类型存储定点数字数据。例如，价值 $123.45 可存储于 NUMERIC(5,2) 字段。请注意，第一个数字（精度）指定总位数。

语法

numeric‑type [ ( precision[, scale] ) ]

参数

默认精度和标度

NUMERIC、DECIMAL、NUMBER 和 MONEY 的默认精度和标度值不同：

支持的编码

Vertica 支持 数字数据类型的以下编码：

  • 精度 ≤ 18：AUTO、BLOCK_DICT、BLOCKDICT_COMP、COMMONDELTA_COMP、DELTAVAL、GCDDELTA 和 RLE

  • 精密 &gt；18：AUTO、BLOCK_DICT、BLOCKDICT_COMP、 RLE

有关详细信息，请参阅编码类型。

数字与整数和浮点数据类型

数字数据类型是存储具有特定精度和标度的值的精确数据类型，通过小数点前后的若干数字表示。这与 Vertica 整数和浮点数据类型形成对比：

  • DOUBLE PRECISION (FLOAT) 支持 ~15 数字、变指数，近似表示数字值。它可能不如 NUMERIC 数据类型精确。

  • INTEGER 支持 ~18 数字，仅限整数。

NUMERIC 数据类型是非整数常量的首选，因为其结果总是精确的。例如：

=> SELECT 1.1 + 2.2 = 3.3;
?column?
----------
 t
(1 row)

=>  SELECT 1.1::float + 2.2::float = 3.3::float;
 ?column?
----------
 f
(1 row)

数字操作

支持的数字操作如下：

  • NUMERIC 除数运算直接运行于数字值，不会转换为浮点数。运算结果具有至少 18 位，且四舍五入。

  • NUMERIC 模数运算（包括%）直接运行于数字值，不会转换为浮点数。结果与分子具有相同标度，无需四舍五入。

  • 一些使用数字数据类型复杂操作会导致隐式转换为 FLOAT。使用 SQRT、STDDEV、超越函数，例如 LOG 以及 TO_CHAR/TO_NUMBER 格式化时，结果总是 FLOAT。

示例

下面的一系列命令创建一个包含数字数据类型的表，并对数据执行一些数学运算。

=> CREATE TABLE num1 (id INTEGER, amount NUMERIC(8,2));

向表中插入一些值：

=> INSERT INTO num1 VALUES (1, 123456.78);

查询表：

=> SELECT * FROM num1;
  id  |  amount
------+-----------
    1 | 123456.78
(1 row)

下面的例子从表 num1 返回 NUMERIC 列、总数：

=> SELECT amount FROM num1;
  amount
-----------
 123456.78
(1 row)

下面的语法在总数上加一 (1)：

=> SELECT amount+1 AS 'amount' FROM num1;
  amount
-----------
 123457.78
(1 row)

下面的语法将总数列乘以 2：

=> SELECT amount*2 AS 'amount' FROM num1;
  amount
-----------
 246913.56
(1 row)

下面的语法为总数列返回负数：

=> SELECT -amount FROM num1;
?column?
------------
-123456.78
(1 row)

下面的语法返回总数实参的绝对值：

=> SELECT ABS(amount) FROM num1;
ABS
-----------
123456.78
(1 row)

下面的语法将 NUMERIC 总数转换为 FLOAT 数据类型：

=> SELECT amount::float FROM num1;
  amount
-----------
 123456.78
(1 row)

另请参阅

算数函数

6.4 - 数值数据类型溢出

Vertica 不检查溢出（正或负），聚合函数 SUM() 除外。如果在使用 SUM 时遇到溢出问题，请使用 SUM_FLOAT() 将其转换为浮点。

有关在将函数 SUM、SUM_FLOAT 和 AVG 用于数值数据类型时 Vertica 如何处理溢出的详细讨论，请参阅SUM、SUM_FLOAT 和 AVG 的数字数据类型溢出。讨论中包括关于关闭静默数值溢出和设置数值数据类型精度的指令。 除以零会返回错误：

=> SELECT 0/0;
ERROR 3117:  Division by zero

=> SELECT 0.0/0;
ERROR 3117:  Division by zero

=> SELECT 0 // 0;
ERROR 3117:  Division by zero

=> SELECT 200.0/0;
ERROR 3117:  Division by zero

=> SELECT 116.43 // 0;
ERROR 3117:  Division by zero

将作为 FLOAT 的零除以零会返回 NaN：

=> SELECT 0.0::float/0;
 ?column?
----------
      NaN
=> SELECT 0.0::float//0;
 ?column?
----------
      NaN

将非零 FLOAT 除以零会返回 Infinity：

=> SELECT 2.0::float/0;
 ?column?
----------
Infinity
=> SELECT 200.0::float//0;
?column?
----------
Infinity

加法、减法和乘法运算忽略溢出。求和运算和求平均运算在内部使用 128 位算术。SUM() 会在最终结果溢出时报错，并建议使用 SUM_FLOAT(INT)，将 128 位总和转换为 FLOAT。例如：

=> CREATE TEMP TABLE t (i INT);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> SELECT SUM(i) FROM t;
   ERROR: sum() overflowed
   HINT: try sum_float() instead
=> SELECT SUM_FLOAT(i) FROM t;
     SUM_FLOAT
---------------------
 2.30584300921369e+19

6.5 - SUM、SUM_FLOAT 和 AVG 的数字数据类型溢出

使用具有 NUMERIC 数据类型的函数 SUM、SUM_FLOAT 和 AVG 时，请注意可能会发生溢出以及 Vertica 如何响应溢出。

此讨论适用于聚合函数和分析函数。

对于查询，当使用具有 NUMERIC 数据类型的函数 SUM、SUM_FLOAT 和 AVG 时，如果超出指定的精度，Vertica 允许静默溢出。

将 SUM 或 SUM_FLOAT 函数与 LAP 一起使用时，Vertica 也允许数字溢出。

默认溢出处理

对于 NUMERIC 数据类型，Vertica 在内部使用 18 位数的倍数。如果您指定的精度小于 18（例如，x(12,0)），Vertica 允许溢出，最多包括 18 的第一个倍数。在某些情况下，如果对列 (SUM(x)) 求和，则可能会超过 Vertica 内部为结果保留的位数。在这种情况下，Vertica 允许静默溢出。

关闭静默数字溢出

您可以关闭静默数字溢出，并指示 Vertica 隐式包含额外的数字位数。通过指定额外的空格，Vertica 可以一致地返回预期结果，即使超出 DDL 中指定的精度。

您可以通过将参数 AllowNumericOverflow 设置为 0 (false) 来关闭静默数字溢出。

当您将该参数设置为 0 时，Vertica 会考虑相应参数 NumericSumExtraPrecisionDigits 的值。

NumericSumExtraPrecisionDigits 参数默认为 6，这意味着 Vertica 在内部添加了超出 DDL 指定精度的六位。添加额外的精度位数可让 Vertica 一致地返回溢出 DDL 指定精度的结果。但是，在内部超出 18 的第二个倍数可能会对性能产生影响。

示例：

  • 假设您的 DDL 指定 11（例如，x(11,0)），并且您接受默认值 NumericSumExtraPrecisionDigits (6)。在这种情况下，Vertica 在内部保持在 18 位数的第一个倍数内，不会产生额外的性能影响。

  • 在相同的示例中，如果将 NumericSumExtraPrecisionDigits 设置为 10，Vertica 会在内部超出阈值，进入 18 的第二个倍数。在性能方面，如果（假设）第一个示例是性能“a”，则第二个示例是“2a”，这大大增加了性能影响。超过 18 的第二个倍数后，性能影响仍然是“2a”。

此示例表示法显示，当您将 AllowNumericOverflow 设置为 0 (false) 时 Vertica 如何在内部做出响应。

如果您希望超过 DDL 中指定的精度，Vertica 建议您关闭静默数字溢出，并设置参数 NumericSumExtraPrecisionDigits。进入 18 的第二个倍数会影响性能。因此，在将 NumericSumExtraPrecisionDigits 设置为高于返回数字列的 SUM 所需的数字之前，请多加考虑。

请注意，如果您关闭 AllowNumericOverflow，并且超出由 NumericSumExtraPrecisionDigits 设置的额外精度位数，Vertica 将返回错误。

对实时聚合投影 (LAP) 的影响

对于 LAP，如果您的 LAP 使用 SUM 或 SUM_FLOAT 函数，Vertica 还允许静默数字溢出。要关闭 LAP 的静默数字溢出：

  1. 将参数 AllowNumericOverflow 设置为 0。

  2. 将参数 NumericSumExtraPrecisionDigits 设置为所需的隐式位数数值。或者，使用默认设置 6。

  3. 删除并重新创建您的 LAP。

如果您关闭静默数字溢出，请注意以下溢出导致回退或错误消息的情况。在这些示例中，AllowNumericOverflow 设置为 0 (false)，每个 LAP 使用 SUM 或 SUM_FLOAT 函数。

当数字溢出关闭时：

  • 负载可以在溢出时回退。

    Vertica 在加载到 LAP 之前聚合数据。如果您正在插入、复制或合并数据，并且在 Vertica 正在聚合数据的加载期间发生溢出，则 Vertica 会回退加载。

  • 加载后可能会发生溢出，因为 Vertica 会对现有数据求和。

    Vertica 对现有数据总和的计算与其在数据加载期间执行的计算是分开的。如果您的 LAP 使用 SUM 或 SUM_FLOAT 选择列并且发生溢出，Vertica 会生成错误消息。此响应的方式类似于 Vertica 使用 SUM 或 SUM_FLOAT 函数为查询生成错误的方式。

  • 在合并期间可能会发生溢出。

    如果 Vertica 在 Tuple Mover 操作期间计算最终总和时发生溢出，Vertica 会在合并期间记录一条消息。如果发生错误，Vertica 会将 LAP 标记为过期。Vertica 不再使用过期的 LAP 运行 Tuple Mover 操作。

7 - 空间数据类型

Vertica 支持两种空间数据类型。这些数据类型在表列中存储二维和三维空间对象：

  • GEOMETRY：在笛卡尔平面中定义的且坐标表示为 (x,y) 对的空间对象。所有计算均使用笛卡尔坐标。
  • GEOGRAPHY：在理想球体的表面或在 WGS84 坐标系中定义的空间对象。坐标表示为经度/纬度角度值，单位为度。所有的计算都以米为单位。对于理想球体计算，球体半径为 6371 千米，与地球形状近似。

GEOMETRY 或 GEOGRAPHY数据类型的最大大小为 10,000,000 字节 (10 MB)。不能将任何一种数据类型用作表的主键。

语法

GEOMETRY [ (length) ]

GEOGRAPHY [ (length) ]

参数

8 - UUID 数据类型

存储通用唯一标识符 (UUID)。UUID 是用于唯一标识记录的 16 字节（128 位）数字。为了生成 UUID，Vertica 提供了函数 UUID_GENERATE，该函数根据高质量随机性从 /dev/urandom 返回 UUID。

语法

UUID

UUID 输入和输出格式

UUID 支持输入不区分大小写的字符串字面量格式，如 RFC 4122 所指定。通常，UUID 被写为十六进制数字序列，在可选地由连字符分隔的几个组中，总共 32 个数字代表 128 位。

以下输入格式有效：

6bbf0744-74b4-46b9-bb05-53905d4538e7
{6bbf0744-74b4-46b9-bb05-53905d4538e7}
6BBF074474B446B9BB0553905D4538E7
6BBf-0744-74B4-46B9-BB05-5390-5D45-38E7

在输出时，Vertica 始终使用以下格式：

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

例如，下表将列 cust_id 定义为 UUID：

=> CREATE TABLE public.Customers
(
    cust_id uuid,
    lname varchar(36),
    fname varchar(24)
);

cust_id 的以下输入使用了几种有效格式：

=>  COPY Customers FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {cede66b7-3d29-4da6-b700-871fc0ac57be}|Kearney|Thomas
>> 34462732ed5649838f3be735b0c32d50|Pham|Duc
>> 9fb0-1de0-1d63-4d09-9415-90e0-b4e9-3b9a|Steinberg|Jeremy
>> \.

在查询此表时，Vertica 以相同的方式格式化所有 cust_id 数据：

=> SELECT cust_id, fname, lname FROM Customers;
                cust_id                | fname  |   lname
--------------------------------------+--------+-----------
 9fb01de0-1d63-4d09-9415-90e0b4e93b9a | Jeremy | Steinberg
 34462732-ed56-4983-8f3b-e735b0c32d50 | Duc    | Pham
 cede66b7-3d29-4da6-b700-871fc0ac57be | Thomas | Kearney
(3 rows)

生成 UUID

您可以使用 Vertica 函数 UUID_GENERATE 自动生成唯一标识表记录的 UUID。例如：

=> INSERT INTO Customers SELECT UUID_GENERATE(),'Rostova','Natasha';
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT cust_id, fname, lname FROM Customers;
               cust_id                |  fname  |   lname
--------------------------------------+---------+-----------
 9fb01de0-1d63-4d09-9415-90e0b4e93b9a | Jeremy  | Steinberg
 34462732-ed56-4983-8f3b-e735b0c32d50 | Duc     | Pham
 cede66b7-3d29-4da6-b700-871fc0ac57be | Thomas  | Kearney
 9aad6757-fe1b-473a-a109-b89b7b358c69 | Natasha | Rostova
(4 rows)

NULL 输入和输出

以下字符串为 UUID 列保留为 NULL：

00000000-0000-0000-0000-000000000000

Vertica 始终将 NULL 呈现为空白。

以下 COPY 语句将 NULL 值显式和隐式插入到 UUID 列中：

=>  COPY Customers FROM STDIN NULL AS 'null';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> null|Doe|Jane
>> 00000000-0000-0000-0000-000000000000|Man|Nowhere
>> \.
=>  COPY Customers FROM STDIN;
>> |Doe|John
>> \.

在所有情况下，Vertica 都将 NULL 呈现为空白：

=> SELECT cust_id, fname, lname FROM Customers WHERE cust_id IS NULL;
 cust_id |  fname  | lname
---------+---------+-------
         | Nowhere | Man
         | Jane    | Doe
         | John    | Doe
(3 rows)

使用限制

UUID 数据类型仅支持也受 CHAR 和 VARCHAR 数据类型支持的关系运算符和函数，例如 MIN、 MAX 和 COUNT。UUID 数据类型不支持数学运算符或函数，例如 SUM 和 AVG。

9 - 数据类型强制转换

Vertica 支持两种数据类型转换：

  • 隐式转换：表达式自动将数据从一种类型转化成另一种类型。

  • 显式转换：SQL 语句指定转换的目标数据类型。

隐式转换

ANSI SQL-92 标准支持相似数据类型间的隐式转换：****

  • 数字类型

  • CHAR、VARCHAR、LONG VARCHAR

  • BINARY、VARBINARY、LONG VARBINARY

Vertica 支持两种类型的标量类型的非标准隐式转换：

  • 从 CHAR 到 FLOAT，以匹配从 VARCHAR 到 FLOAT 的值。以下示例将 CHAR '3' 隐式转换为 FLOAT，这样可以与第二个表达的数值 4.33 相加得到 FLOAT 类型结果。

    => SELECT '3'::CHAR + 4.33::FLOAT;
 ?column?
----------
   7.33
(1 row)

  • DATE 与 TIMESTAMP 之间的转换。以下示例将 DATE 转换为 TIMESTAMP，计算早于上午 12 点 6 时 6 分 6 秒的时间：

    => SELECT DATE('now') - INTERVAL '6:6:6';
      ?column?
---------------------
 2013-07-30 17:53:54
(1 row)

当表达式值的数据类型明确时，会隐式地强制转换，与预期数据类型相匹配。在以下语句中，带引号的字符串常数 '2’ 会隐式地强制转换为 INTEGER 值，使其可作为算术运算符的操作数（加法）：

=> SELECT 2 + '2';
 ?column?
----------
        4
(1 row)

串联运算显式地使用任何数据类型的实参。在以下示例中，串联运算隐式地将算术表达式 2 + 2 和 INTEGER 常数 2 强制转换为 VARCHAR 值，使其可以串联起来。

=> SELECT 2 + 2 || 2;
 ?column?
----------
 42
(1 row)

另一个示例是首先获取今天的日期：

=> SELECT DATE 'now';
  ?column?
------------
 2013-07-31
(1 row)

以下命令将 DATE 转换为 TIMESTAMP，并使用 INTERVAL 在结果中添加一天半：

=> SELECT DATE 'now' + INTERVAL '1 12:00:00';
      ?column?
---------------------
 2013-07-31 12:00:00
(1 row)

大多数隐式转换保留在相关系列中，由粗略到详细朝一个方向进行。例如：

  • DATE 到 TIMESTAMP/TZ

  • INTEGER 到 NUMERIC 再到 FLOAT

  • CHAR 到 FLOAT

  • CHAR 到 VARCHAR

  • CHAR 和/或 VARCHAR 到 FLOAT

  • CHAR 到 LONG VARCHAR

  • VARCHAR 到 LONG VARCHAR

  • BINARY 到 VARBINARY

  • BINARY 到 LONG VARBINARY

  • VARBINARY 到 LONG VARBINARY

更确切地说，数据类型强制转换以此方式在 Vertica 中进行：

没有其他类型转换为或转换自 LONGVARBINARY、VARBINARY 或 BINARY。在以下列表中，<any> 表示这些类型中的一种：INT8、FLOAT8、DATE、TIME、TIMETZ、TIMESTAMP、TIMESTAMPTZ、INTERVAL。

  • <any> -> VARCHAR 为隐式

  • VARCHAR -> <any> 为显式，但 VARCHAR->FLOAT 为隐式

  • <any> <-> CHAR 为显式

  • DATE -> TIMESTAMP/TZ 为隐式

  • TIMESTAMP/TZ -> DATE 为显式，丢失时间

  • TIME -> TIMETZ 为隐式，添加本地时区

  • TIMETZ -> TIME 为显式，丢失时区

  • TIME -> INTERVAL 为隐式，day to second with days=0

  • INTERVAL -> TIME 为显式，截断非时部分

  • TIMESTAMP <-> TIMESTAMPTZ为隐式，调整为本地时区

  • TIMESTAMP/TZ -> TIME 为显式，截断非时部分

  • TIMESTAMPTZ -> TIMETZ 为显式

  • VARBINARY -> LONG VARBINARY 为隐式

  • LONG VARBINARY -> VARBINARY 为显式

  • VARCHAR -> LONG VARCHAR 为隐式

  • LONG VARCHAR -> VARCHAR 为显式

字符串到数字的数据类型转换针对科学记数法、二进制记数法、十六进制和数字型字面量组合，接受带引号常数的格式：

  • 科学记数法:

    => SELECT FLOAT '1e10';
  ?column?
-------------
 10000000000
(1 row)

  • BINARY 缩放:

    => SELECT NUMERIC '1p10';
 ?column?
----------
     1024
(1 row)

  • 十六进制：

    => SELECT NUMERIC '0x0abc';
 ?column?
----------
     2748
(1 row)

复杂类型

集合（arrays 和 sets）可以隐式和显式转换。转换集合时，将转换集合的每个元素。例如，您可以将 ARRAY[VARCHAR] 转换为 ARRAY[INT]，也可以将 SET[DATE] 转换为 SET[TIMESTAMPTZ]。您可以在数组和集合之间进行转换。

当强制转换为有界原生数组时，会截断过长的输入。当转换为非原生数组（即：包含复杂数据类型的数组，其中包括其他数组）时，如果新边界对于数据而言太小，则转换失败

行（结构）既可以隐式转换，也可以显式转换。转换 ROW 时，将转换每个字段值。您可以在转换中指定新字段名称，或仅指定字段类型以使用现有字段名称。

转换可以增加列所需的存储空间。例如，如果将 INT 数组转换为 VARCHAR(50) 数组，则每个元素将占用更多空间，从而导致数组占用更多空间。如果差异非常大或数组有很多元素，这可能意味着数组无法再适合为该列分配的空间。在这种情况下，操作会报告错误并失败。

示例

以下示例将三个字符串转换为 NUMERIC：

=> SELECT NUMERIC '12.3e3', '12.3p10'::NUMERIC, CAST('0x12.3p-10e3' AS NUMERIC);
 ?column? | ?column? |     ?column?
----------+----------+-------------------
    12300 |  12595.2 | 17.76123046875000
(1 row)

以下示例将 VARBINARY 字符串转换为 LONG VARBINARY 数据类型：

=> SELECT B'101111000'::LONG VARBINARY;
 ?column?
----------
 \001x
(1 row)

以下示例将 CHAR 与 LONG VARCHAR 串联，得到 LONG VARCHAR：

=> \set s ''''`cat longfile.txt`''''
=> SELECT length ('a' || :s ::LONG VARCHAR);
 length
----------
 65002
(1 row)

以下示例将 NUMERIC 与 INTEGER 的数据组合转换为 NUMERIC 结果。

=> SELECT (18. + 3./16)/1024*1000;
                ?column?
-----------------------------------------
 17.761230468750000000000000000000000000
(1 row)

另请参阅

10 - 数据类型强制转换表

转换类型

下表定义了 Vertica 支持的所有可能的类型转换。该表第一列的数据类型是待转换的输入内容，第二个标题行所列的数据类型是结果赋值。

隐式和显式转换

Vertica 支持无显式转换的值的数据类型转换，例如 NUMERIC(10,6) -> NUMERIC(18,4)。将类型不同但相互兼容的值转换为目标列的数据类型时，进行隐式数据类型转换。例如，在添加值 (INTEGER + NUMERIC) 时，结果被隐式转换为 NUMERIC 类型以适应语句中的重要类型。根据输入数据的类型，可能会出现不同的精度和小数位数。

当源数据无法隐式转换为目标列的数据类型时，才可进行显式类型转换。

赋值转换

在数据赋值转换中，当赋值到 INSERT 或 UPDATE...SET 语句中的数据库列时，进行隐式强制转换。例如，在包括 INSERT...VALUES('2.5') 的语句中，目标列数据类型是 NUMERIC(18,5)，可推知从 VARCHAR 到列数据类型的转换。

在无数字意义的赋值中，该值需进行 CHAR/VARCHAR/LONG VARCHAR 的比较。

另请参阅

11 - 复杂类型

结构（也称为行）、数组和映射等复杂类型由基元类型组成，有时也由其他复杂类型组成。可以通过以下方式使用复杂类型：

  • 数组和行（任意组合）可用作原生和外部表中的列数据类型。

  • 基元元素类型集可用作原生和外部表中的列数据类型。

  • 数组和行（但不是它们的组合）可以创建为字面量，例如在查询表达式中使用。

MAP 类型是旧类型。要表示映射，请使用 ARRAY[ROW]。

如果 flex 表有一个使用复杂类型的实际列，则该列中的值不包含在 __raw__ 列中。有关详细信息，请参阅将数据加载到 Flex 表实际列。

11.1 - ARRAY

表示数组数据。Vertica 中有两种类型的数组：

  • 原生数组：基元类型的一维数组。原生数组在 TYPES 系统表中跟踪并在原生表中使用。

  • 非原生数组：所有其他受支持的数组，包括包含其他数组（多维数组）或结构 (ROW) 的数组。非原生数组的使用有一些限制。非原生数组在 COMPLEX_TYPES 系统表中跟踪。

两种数组类型的运行方式相同，但它们具有不同的 OID。

数组可以是有界的，这意味着它们指定了最大元素计数，也可以是无界的。无界数组具有最大二进制大小，可以显式设置或默认设置。请参阅元素计数和集合大小的限制。

选定的解析器支持使用 COPY 加载数组。有关详细信息，请参阅各个解析器的文档。

语法

在列定义中：


ARRAY[data_type, max_elements] |
ARRAY[data_type](max_size) |
ARRAY[data_type]

在字面量中：

ARRAY[value[, ...] ]

限制

  • 原生数组仅支持原始类型的数据，例如 int、UUID 等。

  • 强制执行数组维数。列不能包含不同维度的数组。例如，包含三维数组的列只能包含其他三维数组；它不能同时包含一个一维数组。但是，一列中的数组大小可能不同，其中一个数组可以包含四个元素，而另一个数组包含十个元素。

  • 如果指定了数组边界，则对所有加载或更改数据的操作强制执行。无界数组可能包含与分配的二进制大小一样多的元素。

  • 数组具有最大二进制大小。如果在定义数组时未设置此大小，则使用默认值。

  • 数组不支持 LONG 类型（如 LONG VARBINARY 或 LONG VARCHAR）或用户定义类型（如 Geometry）。

列定义的语法

列定义中使用的数组可以是有界或无界的。有界数组必须指定最大元素数。无界数组可以指定数组的最大二进制大小（以字节为单位），或者使用 DefaultArrayBinarySize 的值。您可以指定边界或二进制大小，但不能同时指定两者。有关这些值的更多信息，请参阅元素计数和集合大小限制。

以下示例使用无界数组为客户定义了一个表：

=> CREATE TABLE customers (id INT, name VARCHAR, email ARRAY[VARCHAR(50)]);

以下示例对客户电子邮件地址使用有界数组，对订单历史记录使用无界数组：

=> CREATE TABLE customers (id INT, name VARCHAR, email ARRAY[VARCHAR(50),5], orders ARRAY[INT]);

以下示例使用具有 ROW 元素的数组：

=> CREATE TABLE orders(
  orderid INT,
  accountid INT,
  shipments ARRAY[
    ROW(
      shipid INT,
      address ROW(
        street VARCHAR,
        city VARCHAR,
        zip INT
        ),
      shipdate DATE
    )
  ]
 );

要声明多维数组，请使用嵌套。例如，ARRAY[ARRAY[int]] 指定一个二维数组。

直接构造的语法（字面量）

使用 ARRAY 关键字构造数组值。下面的示例创建一个整数值数组。

=> SELECT ARRAY[1,2,3];
 array
-------
 [1,2,3]
(1 row)

您可以将一个数组嵌套在另一个数组中，如下例所示。

=> SELECT ARRAY[ARRAY[1],ARRAY[2]];
   array
-----------
 [[1],[2]]
(1 row)

如果一系列数组不包含 null 元素且无函数调用，则可以缩写语法：

=> SELECT ARRAY[[1,2],[3,4]];
     array
---------------
 [[1,2],[3,4]]
(1 row)

---not valid:
=> SELECT ARRAY[[1,2],null,[3,4]];

ERROR 4856:  Syntax error at or near "null" at character 20
LINE 1: SELECT ARRAY[[1,2],null,[3,4]];
                           ^

数组字面量可以包含所有标量类型（ROW 和 ARRAY）的元素。ROW 元素必须都具有相同的字段集：

=> SELECT ARRAY[ROW(1,2),ROW(1,3)];
               array
-----------------------------------
 [{"f0":1,"f1":2},{"f0":1,"f1":3}]
(1 row)

=> SELECT ARRAY[ROW(1,2),ROW(1,3,'abc')];
ERROR 3429:  For 'ARRAY', types ROW(int,int) and ROW(int,int,unknown) are inconsistent

因为在直接构造数组时元素是已知的，所以这些数组是隐式有界的。

您可以在比较中使用 ARRAY 字面量，如下例所示：

=> SELECT id.name, id.num, GPA FROM students
   WHERE major = ARRAY[ROW('Science','Physics')];
 name  | num | GPA
-------+-----+-----
 bob   | 121 | 3.3
 carol | 123 | 3.4
(2 rows)

输出格式

数组列的查询返回 JSON 格式，值显示在括号中的逗号分隔列表中。以下示例显示了一个包含数组列的查询。

=> SELECT cust_custkey,cust_custstaddress,cust_custcity,cust_custstate from cust;
cust_custkey |               cust_custstaddress                      |                cust_custcity                | cust_custstate
-------------+-------  ----------------------------------------------+---------------------------------------------+----------------
      342176 | ["668 SW New Lane","518 Main Ave","7040 Campfire Dr"] | ["Winchester","New Hyde Park","Massapequa"] | ["VA","NY","NY"]
      342799 | ["2400 Hearst Avenue","3 Cypress Street"]             | ["Berkeley","San Antonio"]                  | ["CA","TX"]
      342845 | ["336 Boylston Street","180 Clarkhill Rd"]            | ["Boston","Amherst"]                        | ["MA","MA"]
      342321 | ["95 Fawn Drive"]                                     | ["Allen Park"]                              | ["MI"]
      342989 | ["5 Thompson St"]                                     | ["Massillon"]                               | ["OH"]
(5 rows)

请注意，JSON 格式转义的一些字符在原生 VARCHAR 中不会转义。例如，如果您将 "c:\users\data" 插入到数组中，则该值的 JSON 输出为 "c:\\\users\\\data"。

元素访问

数组是从 0 开始索引的。第一个元素的序号位置为 0，第二个元素的序号位置为 1，依此类推。

您可以通过索引访问（取消引用）数组中的元素：

=> SELECT (ARRAY['a','b','c','d','e'])[1];
array
-------
b
(1 row)

要指定范围，请使用格式 start:end。不包括范围的结尾。

=> SELECT(ARRAY['a','b','c','d','e','f','g'])[1:4];
array
---------
["b","c","d"]
(1 row)

要从多维数组中取消引用某个元素，请将每个索引放在括号中：

=> SELECT(ARRAY[ARRAY[1,2],ARRAY[3,4]])[0][0];
 array
-------
 1
(1 row)

超过边界的索引引用会返回 NULL。

元素计数和集合大小的限制

在为表列声明集合类型时，您可以限制元素的数量或集合的总二进制大小。在查询处理期间，Vertica 始终根据元素计数或二进制大小保留列所需的最大内存。如果此大小比您的数据实际需要的大得多，则设置这些限制之一可以通过减少必须为列保留的内存量来提高查询性能。

您可以通过强制转换来更改集合的边界，包括在有界和无界集合之间进行更改。请参阅 强制转换。

有界集合指定最大元素计数。有界集合列中的值可能包含较少的元素，但可能不会包含更多。任何将比声明的最大值更多的元素插入有界集合的尝试都是错误的。有界集合的二进制大小是数据类型大小和最大元素数的乘积，可能向上取整。

无界集合显式或隐式指定二进制大小（以字节为单位）。它可能包含尽可能多的元素，以适应该二进制大小。

如果嵌套数组为所有维度指定边界，Vertica 会设置一个边界，该边界是这些边界的乘积。在以下示例中，内部和外部数组的边界均为 10，但仅强制执行总元素计数 100。

ARRAY[ARRAY[INT,10],10]

如果嵌套数组仅指定外部集合的边界，则将其视为总边界。上一示例等效于以下示例：

ARRAY[ARRAY[INT],100]

您必须为所有嵌套集合指定边界或仅为外部集合指定边界。对于任何其他边界分布，Vertica 会将集合视为无界。

您可以为无界集合指定最大二进制大小，而不是指定边界。无论集合包含多少元素，二进制大小都是绝对限制。未指定最大二进制大小的集合使用 DefaultArrayBinarySize 的值。此大小在定义集合时设置，不受以后对 DefaultArrayBinarySize 值的更改的影响。

您不能为有界集合设置最大二进制大小，只能设置无界集合。

您可以使用 ALTER TABLE 更改数组列的边界或二进制大小，如下例所示：

=> ALTER TABLE cust ALTER COLUMN orders SET DATA TYPE ARRAY[INTEGER](100);

如果更改减少了集合的大小并导致数据丢失，则更改将失败。

比较

所有集合都支持相等 (=)、不等 (<>) 和 null 安全相等 (<=>)。一维集合还支持以下集合之间的比较运算符 (<、<=、>、>=) 相同的类型（数组或集合）。比较遵循以下规则：

  • 空集合最后排序。

  • 使用元素数据类型的排序规则逐个元素比较非空集合。第一对不相等元素的相对顺序决定了两个集合的顺序。

  • 如果两个集合中的所有元素都等于较短集合的长度，则较短集合在较长集合之前排序。

  • 如果两个集合中的所有元素相等且集合的长度相等，则集合相等。

NULL 处理

集合的空语义在大多数方面与普通列一致。有关空处理的更多信息，请参阅 NULL 排序顺序。

当集合为 null 而不是空时，空安全相等运算符 (<=>) 的行为与相等 (=) 不同。将集合严格地与 NULL 进行比较是未定义的。

=> SELECT ARRAY[1,3] = NULL;
?column?
----------

(1 row)

=> SELECT ARRAY[1,3] <=> NULL;
 ?column?
----------
 f
(1 row)

在以下示例中，表中的授予列对于员工 99 为 null。

=> SELECT grants = NULL FROM employees WHERE id=99;
 ?column?
----------

(1 row)

=> SELECT grants <=> NULL FROM employees WHERE id=99;
 ?column?
----------
 t
(1 row)

空集合不为 null 并且按预期运行。

=> SELECT ARRAY[]::ARRAY[INT] = ARRAY[]::ARRAY[INT];
 ?column?
----------
 t
(1 row)

集合逐个元素进行比较。如果比较依赖于 null 元素，则结果是未知的 (null)，而不是 false。例如，ARRAY[1,2,null]=ARRAY[1,2,null] 和 ARRAY[1,2,null]=ARRAY[1,2,3] 都返回 null，但 ARRAY[1,2,null]=ARRAY[1,4,null] 因为第二个元素不匹配而返回 false。

强制转换

转换一个数组会转换数组的每个元素。因此，您可以按照与标量值转换相同的规则在数据类型之间转换。

您可以显式地转换字面量数组和数组列：

=> SELECT ARRAY['1','2','3']::ARRAY[INT];
  array
---------
[1,2,3]
(1 row)

您可以通过强制转换来更改数组或集的边界。当强制转换为有界原生数组时，会截断太长的输入。当转换为非原生数组（包含复杂数据类型（包括其他数组）的数组）时，如果新边界对于数据而言太小，则转换失败：

=> SELECT ARRAY[1,2,3]::ARRAY[VARCHAR,2];
   array
-----------
 ["1","2"]
(1 row)

=> SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]::ARRAY[ARRAY[VARCHAR,2],2];
ERROR 9227:  Output array isn't big enough
DETAIL:  Type limit is 4 elements, but value has 6 elements

如果转换为有界多维数组，则必须在所有级别指定边界：

=> SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]::ARRAY[ARRAY[VARCHAR,5],10];
             array
-------------------------------
 [["1","2","3"],["4","5","6"]]
(1 row)

=> SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]::ARRAY[ARRAY[VARCHAR,2]];
WARNING 9753:  Collection type bound will not be used
DETAIL:  A bound was provided for an inner dimension, but not for an outer dimension
             array
-------------------------------
 [["1","2","3"],["4","5","6"]]
(1 row)

赋值转换和隐式转换的工作方式与标量相同：

=> CREATE TABLE transactions (tid INT, prod_ids ARRAY[VARCHAR,100], quantities ARRAY[INT,100]);
CREATE TABLE

=> INSERT INTO transactions VALUES (12345, ARRAY['p1265', 'p4515'], ARRAY[15,2]);
 OUTPUT
--------
      1
(1 row)

=> CREATE TABLE txreport (prod_ids ARRAY[VARCHAR(12),100], quants ARRAY[VARCHAR(32),100]);
CREATE TABLE

=> INSERT INTO txreport SELECT prod_ids, quantities FROM transactions;
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM txreport;
     prod_ids      |   quants
-------------------+------------
 ["p1265","p4515"] | ["15","2"]
(1 row)

您可以在 ARRAY 和 SET 类型（仅限原生数组）之间执行显式转换，但不能执行隐式转换。如果集合是无界的且数据类型未更改，则保留二进制大小。例如，如果将 ARRAY[INT] 转换为 SET[INT]，则该集与数组具有相同的二进制大小。

如果从一种元素类型转换为另一种元素类型，则生成的集合使用默认二进制大小。如果这会导致数据不适合，则转换失败。

您不能从数组转换为具有不同维度的数组，例如从二维数组转换为一维数组。

函数和运算符

有关可用于操作数组和集合的函数的完整列表，请参阅 集合函数。

可以通过以下方式使用集合：

集合不能以下列方式使用：

  • 作为 IN 或 NOT IN 表达式的一部分。

  • 创建表时作为分区列。

  • 使用 ANALYZE_STATISTICS 或 TopK 预测。

  • 仅限非原生数组：ORDER BY、PARTITION BY、DEFAULT、SET USING 或约束。

11.2 - MAP

仅以 Parquet、ORC 和 Avro 格式表示外部表中的映射数据。MAP 只能使用基元类型，而且不能包含其他复杂类型。您可以在表定义中使用 MAP 类型来利用数据中的列，但不能查询这些列。

MAP 的更好替代方法是 ARRAY[ROW]。行数组可以使用所有受支持的复杂类型并且可以查询。这是 INFER_TABLE_DDL 建议的表示。对于 Avro 数据，ROW 必须具有名为 key 和 value 的字段。

在单个表中，您必须使用相同的方法（MAP 或 ARRAY[ROW]）定义所有映射列。

语法

在列定义中：

MAP<key,value>

列定义的映射输入格式

在外部表的列定义中，MAP 由指定为类型的键值对组成。以下示例中的表定义了产品 ID 到名称的映射。

=> CREATE EXTERNAL TABLE store (storeID INT, inventory MAP<INT,VARCHAR(100)>)
    AS COPY FROM '...' PARQUET;

11.3 - ROW

表示结构化数据（结构）。ROW 可以包含 Vertica 支持的任何基元类型或复杂类型的字段。

语法

在列定义中：

  • ROW([field] type[, ...])

    如果省略字段名称，Vertica 会生成以“f0”开头的名称。

  • 在字面量中：

    ROW(value [AS field] [, ...]) [AS name(field[, ...])]

列定义的语法

在列定义中，ROW 由一个或多个以逗号分隔的字段名和类型对组成。在以下示例中，Parquet 数据文件包含地址的结构，其在外部表中作为 ROW 读取：

=> CREATE EXTERNAL TABLE customers (name VARCHAR,
    address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

ROW 可以嵌套；一个字段可以有一个 ROW 类型：

=> CREATE TABLE employees(
    employeeID INT,
    personal ROW(
      name VARCHAR,
      address ROW(street VARCHAR, city VARCHAR, zipcode INT),
      taxID INT),
    department VARCHAR);

ROW 可以包含数组：

=> CREATE TABLE customers(
  name VARCHAR,
  contact ROW(
    street VARCHAR,
    city VARCHAR,
    zipcode INT,
    email ARRAY[VARCHAR]
  ),
  accountid INT );

加载数据时，表定义中的基元类型必须与数据中的基元类型相匹配。ROW 结构也必须匹配；ROW 必须包含且仅包含数据中结构的所有字段。

ROW 列的限制

ROW 列有几个限制：

  • 最大嵌套深度为 100。

  • Vertica 表最多支持 9800 个列和字段。不计算 ROW 本身，只计算其字段。

  • ROW 列不能使用任何约束（例如 NOT NULL）或默认值。

  • ROW 字段不能是 auto_increment 或 setof。

  • ROW 定义必须至少包含一个字段。

  • “Row”是 ROW 定义中的保留关键字，但允许作为表或列的名称。

  • 不能使用 ALTER TABLE...ALTER COLUMN 修改 ROW 列。

  • 包含 ROW 列的表也不能包含标识、自动增量、默认、SET USING 或序列列。

直接构造的语法（字面量）

在字面量中，例如比较操作中的值，ROW 由一个或多个值组成。如果您在 ROW 表达式中省略字段名称，Vertica 会自动生成它们。如果您不强制转换类型，Vertica 会根据数据值推断类型。

=> SELECT ROW('Amy',2,false);
                row
--------------------------------------------
 {"f0":"Amy","f1":2,"f2":false}
(1 row)

您可以使用 AS 子句来命名 ROW 及其字段：

=> SELECT ROW('Amy',2,false) AS student(name, id, current);
               student
--------------------------------------------
 {"name":"Amy","id":2,"current":false}
(1 row)

您还可以使用 AS 命名单个字段。此查询产生的输出与之前的输出相同：

=> SELECT ROW('Amy' AS name, 2 AS id, false AS current) AS student;

您无需命名所有字段。

在 ROW 元素数组中，如果您使用 AS 命名字段且元素之间的名称不同，Vertica 会为所有元素使用最右边的名称：

=> SELECT ARRAY[ROW('Amy' AS name, 2 AS id),ROW('Fred' AS first_name, 4 AS id)];
                           array
------------------------------------------------------------
 [{"first_name":"Amy","id":2},{"first_name":"Fred","id":4}]
(1 row)

您可以显式强制转换类型：

=> SELECT ROW('Amy',2.5::int,false::varchar);
               row
------------------------------------------
 {"f0":"Amy","f1":3,"f2":"f"}
(1 row)

使用单引号转义字面量输入中的单引号，如以下示例所示：

=> SELECT ROW('Howard''s house',2,false);
                      row
---------------------------------------------------
 {"f0":"Howard's house","f1":2,"f2":false}
(1 row)

您可以使用所有标量类型、ROW 和 ARRAY 的字段，如以下示例所示：

=> SELECT id.name, major, GPA FROM students
   WHERE id = ROW('alice',119, ARRAY['alice@example.com','ap16@cs.example.edu']);
 name  |               major                | GPA
-------+------------------------------------+-----
 alice | [{"school":"Science","dept":"CS"}] | 3.8
(1 row)

输出格式

ROW 值以 JSON 格式输出，如以下示例所示。

=> CREATE EXTERNAL TABLE customers (name VARCHAR,
    address ROW(street VARCHAR, city VARCHAR, zipcode INT))
    AS COPY FROM '...' PARQUET;

=> SELECT address FROM customers WHERE address.city ='Pasadena';
                              address
--------------------------------------------------------------------
 {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001}
 {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
 {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001}
 {"street":"15 Raymond Dr","city":"Pasadena","zipcode":91003}
(4 rows)

下表指定了从 Vertica 数据类型到 JSON 数据类型的映射。

比较

ROW 支持在具有相同字段集的输入之间使用相等 (=)、不相等 (<>) 和空安全相等 (<=>)。仅包含基元类型的 ROW（包括基元类型的嵌套 ROW）也支持比较运算符（<、<=、>、>=）。

当且仅当所有字段都相等时，两个 ROW 相等。Vertica 按顺序比较字段，直到发现不相等或已比较了所有字段。第一个不相等字段的求值决定哪个 ROW 更大：

=> SELECT ROW(1, 'joe') > ROW(2, 'bob');
?column?
----------
f
(1 row)

具有不同架构的 ROW 之间的比较失败：

=> SELECT ROW(1, 'joe') > ROW(2, 'bob', 123);
ERROR 5162:  Unequal number of entries in row expressions

如果比较结果依赖于 null 字段，则结果为 null：

=>  select row(1, null, 3) = row(1, 2, 3);
 ?column?
----------

(1 row)

NULL 处理

如果结构存在但字段值为 null，Vertica 会将 NULL 作为其在 ROW 中的值。所有字段为 null 的结构被视为具有 null 字段的 ROW。如果结构本身为 null，Vertica 会将 ROW 读取为 NULL。

强制转换

转换 ROW 将转换每个字段。因此，您可以按照与标量值转换相同的规则在数据类型之间转换。

以下示例将转换客户表中的 contact ROW，将 zipcode 字段从 INT 更改为 VARCHAR 并向数组添加边界：

=> SELECT contact::ROW(VARCHAR,VARCHAR,VARCHAR,ARRAY[VARCHAR,20]) FROM customers;
                                                         contact

--------------------------------------------------------------------------------
-----------------------------------------
 {"street":"911 San Marcos St","city":"Austin","zipcode":"73344","email":["missy@mit.edu","mcooper@cern.gov"]}
 {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":"91001","email":["shelly@meemaw.name","cooper@caltech.edu"]}
 {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":"91001","email":["hofstadter@caltech.edu"]}
 {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":"91001","email":[]}
 {"street":null,"city":"Pasadena","zipcode":"91001","email":["raj@available.com"]}

(6 rows)

您可以指定新字段名称以在输出中更改它们：

=> SELECT contact::ROW(str VARCHAR, city VARCHAR, zip VARCHAR, email ARRAY[VARCHAR,
20]) FROM customers;
                                                     contact

--------------------------------------------------------------------------------
----------------------------------
 {"str":"911 San Marcos St","city":"Austin","zip":"73344","email":["missy@mit.edu","mcooper@cern.gov"]}
 {"str":"100 Main St Apt 4B","city":"Pasadena","zip":"91001","email":["shelly@meemaw.name","cooper@caltech.edu"]}
 {"str":"100 Main St Apt 4A","city":"Pasadena","zip":"91001","email":["hofstadter@caltech.edu"]}
 {"str":"23 Fifth Ave Apt 8C","city":"Pasadena","zip":"91001","email":[]}
 {"str":null,"city":"Pasadena","zip":"91001","email":["raj@available.com"]}
(6 rows)

支持的运算符和谓词

可以通过以下方式在查询中使用 ROW 值：

  • INNER 和 OUTER JOIN

  • 比较，IN、BETWEEN（仅限不可为空的筛选器）

  • IS NULL、IS NOT NULL

  • CASE

  • GROUP BY、ORDER BY

  • SELECT DISTINCT

  • 用户定义的标量、变换和分析函数的实参

ROW 值不支持以下运算符和谓词：

  • 数学运算符

  • 整行类型强制转换（支持字段值强制转换）

  • BITWISE、LIKE

  • MLA (ROLLUP, CUBE, GROUPING SETS)

  • 聚合函数，包括 MAX、MIN 和 SUM

  • 集运算符，包括 UNION、UNION ALL、MINUS 和 INTERSECT

从用户定义的标量函数返回的 ROW 不支持 COUNT，但 ROW 列和字面量支持 COUNT。

在比较操作（包括 ORDER BY 之类的隐式比较）中，ROW 字面量被视为其字段值的序列。例如，以下两个语句是等效的：

GROUP BY ROW(zipcode, city)
GROUP BY zipcode, city

在视图和子查询中使用行

您可以使用 ROW 列来构造视图和子查询。考虑具有以下定义的员工和客户表：

=> CREATE EXTERNAL TABLE customers(name VARCHAR,
        address ROW(street VARCHAR, city VARCHAR, zipcode INT), accountID INT)
    AS COPY FROM '...' PARQUET;

=> CREATE EXTERNAL TABLE employees(employeeID INT,
        personal ROW(name VARCHAR,
        address ROW(street VARCHAR, city VARCHAR, zipcode INT),
        taxID INT), department VARCHAR)
    AS COPY FROM '...' PARQUET;

以下示例将创建一个视图并对其进行查询。

=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(address.city, address.zipcode)
FROM customers GROUP BY address.city, address.zipcode;
CREATE VIEW

=> SELECT employees.personal.name, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND neighbors.nu
m_neighbors > 1;
        name        |                area
--------------------+-------------------------------------
 Sheldon Cooper     | {"city":"Pasadena","zipcode":91001}
 Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)

11.4 - SET

表示无序的、唯一元素的集合。集可能只包含基元类型。与数组不同，在集中，元素位置是没有意义的。

集不支持 LONG 类型（如 LONG VARBINARY 或 LONG VARCHAR）或用户定义类型（如 Geometry）。

如果您从数组填充集，Vertica 会对值进行排序并移除重复元素。如果您不关心元素位置并计划运行检查特定元素是否存在的查询（查找、包含），则使用集可以提高查询性能。

集可以是有界的（这意味着它们指定了最大元素计数），也可以是无界的。无界集具有最大二进制大小，可以显式设置或默认设置。请参阅元素计数和集合大小的限制。

语法

在列定义中：


SET[data_type, max_elements] |
SET[data_type](max_size) |
SET[data_type]

在字面量中：

SET[value[, ...] ]

限制

  • 集仅支持基元类型的数据，例如 int、UUID 等。

  • 如果指定了边界，则会对加载或更改数据的所有操作强制执行边界。无界集可以具有适合所分配的二进制大小的任意数量的元素。

  • 集具有最大二进制大小。如果在定义集时未设置此大小，则使用默认值。

列定义的语法

列定义中使用的集可以是有界或无界的。有界集必须指定最大元素数。无界集可以指定集的最大二进制大小，也可以使用 DefaultArrayBinarySize 的值。您可以指定边界或二进制大小，但不能同时指定两者。有关这些值的更多信息，请参阅元素计数和集合大小限制。

以下示例定义了一个具有无界集列的表。

=> CREATE TABLE users
(
user_id INTEGER,
display_name VARCHAR,
email_addrs SET[VARCHAR]
);

当您将数组数据加载到定义为集的列中时，数组数据会自动转换为集。

直接构造的语法（字面量）

使用 SET 关键字构造集值。字面量设置值包含在括号中。例如，要创建一个 INT 集，您将执行以下操作：

=> SELECT SET[1,2,3];
  set
-------
 [1,2,3]
(1 row)

您可以通过转换将数组显式转换为集，如以下示例所示：

=> SELECT ARRAY[1, 5, 2, 6, 3, 0, 6, 4]::SET[INT];
     set
-----------------
[0,1,2,3,4,5,6]
(1 row)

请注意，重复元素已移除并且元素已排序。

因为在直接构造集时元素是已知的，所以这些集是隐式有界的。

输出格式

集以类似 JSON 的格式显示，括号中包含逗号分隔的元素（如数组）。在以下示例中，email_addrs 列是一个集。

=> SELECT custkey,email_addrs FROM customers LIMIT 4;
 custkey |                           email_addrs
---------+------------------------------------------------------------------------
 342176  | ["joe.smith@example.com"]
 342799  | ["bob@example,com","robert.jones@example.com"]
 342845  | ["br92@cs.example.edu"]
 342321  | ["789123@example-isp.com","sjohnson@eng.example.com","sara@johnson.example.name"]

元素计数和集合大小的限制

在为表列声明集合类型时，您可以限制元素的数量或集合的总二进制大小。在查询处理期间，Vertica 始终根据元素计数或二进制大小保留列所需的最大内存。如果此大小比您的数据实际需要的大得多，则设置这些限制之一可以通过减少必须为列保留的内存量来提高查询性能。

您可以通过强制转换来更改集合的边界，包括在有界和无界集合之间进行更改。请参阅 强制转换。

有界集合指定最大元素计数。有界集合列中的值可能包含较少的元素，但可能不会包含更多。任何将比声明的最大值更多的元素插入有界集合的尝试都是错误的。有界集合的二进制大小是数据类型大小和最大元素数的乘积，可能向上取整。

无界集合显式或隐式指定二进制大小（以字节为单位）。它可能包含尽可能多的元素，以适应该二进制大小。

您可以为无界集合指定最大二进制大小，而不是指定边界。无论集合包含多少元素，二进制大小都是绝对限制。未指定最大二进制大小的集合使用 DefaultArrayBinarySize 的值。此大小在定义集合时设置，不受以后对 DefaultArrayBinarySize 值的更改的影响。

您不能为有界集合设置最大二进制大小，只能设置无界集合。

比较

所有集合都支持相等 (=)、不等 (<>) 和 null 安全相等 (<=>)。一维集合还支持以下集合之间的比较运算符 (<、<=、>、>=) 相同的类型（数组或集合）。比较遵循以下规则：

  • 空集合最后排序。

  • 使用元素数据类型的排序规则逐个元素比较非空集合。第一对不相等元素的相对顺序决定了两个集合的顺序。

  • 如果两个集合中的所有元素都等于较短集合的长度，则较短集合在较长集合之前排序。

  • 如果两个集合中的所有元素相等且集合的长度相等，则集合相等。

Null 处理

集合的空语义在大多数方面与普通列一致。有关空处理的更多信息，请参阅 NULL 排序顺序。

当集合为 null 而不是空时，空安全相等运算符 (<=>) 的行为与相等 (=) 不同。将集合严格地与 NULL 进行比较是未定义的。

=> SELECT ARRAY[1,3] = NULL;
?column?
----------

(1 row)

=> SELECT ARRAY[1,3] <=> NULL;
 ?column?
----------
 f
(1 row)

在以下示例中，表中的授予列对于员工 99 为 null。

=> SELECT grants = NULL FROM employees WHERE id=99;
 ?column?
----------

(1 row)

=> SELECT grants <=> NULL FROM employees WHERE id=99;
 ?column?
----------
 t
(1 row)

空集合不为 null 并且按预期运行。

=> SELECT ARRAY[]::ARRAY[INT] = ARRAY[]::ARRAY[INT];
 ?column?
----------
 t
(1 row)

集合逐个元素进行比较。如果比较依赖于 null 元素，则结果是未知的 (null)，而不是 false。例如，ARRAY[1,2,null]=ARRAY[1,2,null] 和 ARRAY[1,2,null]=ARRAY[1,2,3] 都返回 null，但 ARRAY[1,2,null]=ARRAY[1,4,null] 因为第二个元素不匹配而返回 false。

强制转换

转换一个集会转换集的每个元素。因此，您可以按照与标量值转换相同的规则在数据类型之间转换。

您可以显式地转换字面量集和集列：

=> SELECT SET['1','2','3']::SET[INT];
   set
---------
[1,2,3]
(1 row)

=> CREATE TABLE transactions (tid INT, prod_ids SET[VARCHAR], quantities SET[VARCHAR(32)]);

=> INSERT INTO transactions VALUES (12345, SET['p1265', 'p4515'], SET['15','2']);

=> SELECT quantities :: SET[INT] FROM transactions;
 quantities
------------
   [15,2]
(1 row)

赋值转换和隐式转换的工作方式与标量相同。

您可以在 ARRAY 和 SET 类型之间执行显式转换，但不能执行隐式转换。如果集合是无界的且数据类型未更改，则保留二进制大小。例如，如果将 ARRAY[INT] 转换为 SET[INT]，则该集与数组具有相同的二进制大小。

在将数组转换为集时，Vertica 会首先转换每个元素，然后对集进行排序并移除重复项。如果将两个源值转换为相同的目标值，则其中一个将被移除。例如，如果您将 FLOAT 数组转换为一组 INT，则数组中的两个值可能会四舍五入为相同的整数，然后被视为重复项。如果数组包含多个转换为 NULL 的值，也会发生这种情况。

如果从一种元素类型转换为另一种元素类型，则生成的集合使用默认二进制大小。如果这会导致数据不适合，则转换失败。

函数和运算符

有关可用于操作数组和集合的函数的完整列表，请参阅 集合函数。

可以通过以下方式使用集合：

集合不能以下列方式使用：

  • 作为 IN 或 NOT IN 表达式的一部分。

  • 创建表时作为分区列。

  • 使用 ANALYZE_STATISTICS 或 TopK 预测。

  • 仅限非原生数组：ORDER BY、PARTITION BY、DEFAULT、SET USING 或约束。

12 - Vertica 和 Oracle 之间的数据类型映射

Oracle 对所有主要数据类型使用专用数据类型，例如 VARCHAR、INTEGER、FLOAT、DATE。在将数据库从 Oracle 迁移到 Vertica 之前，首先转换架构，以最大限度地减少错误和修复错误数据问题所花费的时间。

下表比较了 Oracle 数据类型与 Vertica 数据类型的行为。