数据类型

ProtonBase 是一款兼容 PostgreSQL (PG) 协议的分布式数据库，支持丰富的数据类型，包括基本数据类型、复合类型和特殊类型，适用于大规模数据存储和高并发查询场景。

本文档重点介绍 ProtonBase 支持的 基本数据类型 和 复合数据类型，包含各类型的取值范围说明。

基本数据类型

数值类型

字符串类型

日期和时间类型

布尔类型

二进制类型

复合数据类型

数组类型

支持存储同类型的多个值，最大维度 6 维，单元素大小受 TOAST 存储限制（通常 1 GB）。

示例：

-- 定义数组列
CREATE TABLE products (
 id INT,
 tags VARCHAR(20)[] -- 字符串数组
);
 
-- 插入数据
INSERT INTO products VALUES (1, ARRAY['electronics', 'gadget']);
 
-- 查询
SELECT tags[1] FROM products WHERE id = 1; -- 返回 'electronics'

JSON / JSONB

示例：

CREATE TABLE user_profiles (
 id INT,
 profile JSONB
);
 
INSERT INTO user_profiles VALUES (1, '{"name": "Alice", "age": 30}');
 
-- 查询 JSON 字段
SELECT profile->>'name' FROM user_profiles WHERE id = 1; -- 返回 "Alice"

特殊数据类型

UUID

存储 128 位全局唯一标识符，格式为 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx。

示例：

CREATE TABLE orders (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 amount DECIMAL(10,2)
);

枚举类型（ENUM）

定义固定值的字符串枚举，最多支持 1,000 个枚举值。

示例：

CREATE TYPE status AS ENUM ('pending', 'completed', 'failed');
 
CREATE TABLE tasks (
 id INT,
 task_status status
);
 
INSERT INTO tasks VALUES (1, 'pending');

类型转换

ProtonBase 支持在查询和数据操作过程中进行数据类型转换，包括隐式转换和显式转换两种方式。

隐式类型转换

在某些操作中，ProtonBase 会自动进行类型转换以确保操作的正确执行。例如，在比较不同类型的数值时，系统会自动将它们转换为相同的类型。

示例：

-- INT 和 BIGINT 之间的隐式转换
SELECT * FROM users WHERE id = 123456789012; -- id 为 INT 类型，但值超出范围时会自动处理
 
-- 字符串和数值之间的隐式转换
SELECT * FROM products WHERE price = '99.99'; -- price 为 DECIMAL 类型，字符串会自动转换

显式类型转换

用户可以使用 CAST 函数或 :: 操作符显式地进行类型转换。

使用 CAST 函数

-- 将字符串转换为整数
SELECT CAST('123' AS INT);
 
-- 将数值转换为字符串
SELECT CAST(123.45 AS VARCHAR(10));
 
-- 将字符串转换为日期
SELECT CAST('2023-10-01' AS DATE);

使用 :: 操作符

-- 将字符串转换为整数
SELECT '123'::INT;
 
-- 将数值转换为字符串
SELECT 123.45::VARCHAR(10);
 
-- 将字符串转换为日期
SELECT '2023-10-01'::DATE;
 
-- 将时间戳转换为日期
SELECT '2023-10-01 14:30:00'::TIMESTAMP::DATE;
 
-- 时区相关转换
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ; -- 转换为带时区的时间戳
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai'; -- 为时间戳添加时区信息
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ AT TIME ZONE 'UTC'; -- 转换时区
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ::TIMESTAMP; -- 去除时区信息

类型转换规则

1. 数值类型转换：较小范围的数值类型可以隐式转换为较大范围的数值类型（如 INT 到 BIGINT），但反过来可能需要显式转换并可能丢失精度。

2. 字符串与数值转换：字符串可以转换为数值类型，前提是字符串内容符合数值格式。

3. 日期时间转换：字符串可以转换为日期时间类型，需要符合标准的日期时间格式。

4. JSON 类型转换：JSON/JSONB 类型可以与字符串类型相互转换。

5. 时区转换：TIMESTAMP 和 TIMESTAMPTZ 之间可以相互转换，但需要注意时区信息的处理。

注意事项

• 类型转换可能导致数据精度丢失或转换失败
• 在进行类型转换时，应确保源数据格式与目标类型兼容
• 显式类型转换比隐式类型转换更安全，建议在关键业务逻辑中使用显式转换
• 时区转换时要注意时区信息的正确性，避免因时区处理不当导致的时间偏差

转换失败处理

在进行类型转换时，如果源数据格式与目标类型不兼容，将会导致转换失败并抛出错误。以下是常见的转换失败场景及处理方法：

常见转换失败场景

1. 无效的数值格式转换

   -- 错误示例：包含非数字字符的字符串转换为数值
   SELECT CAST('123abc' AS INT); -- 错误：invalid input syntax for type integer
    
   -- 正确处理方式：使用正则表达式预检查或异常处理
   SELECT CASE WHEN '123abc' ~ '^\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;

2. 超出范围的数值转换

   -- 错误示例：超出目标类型范围的数值转换
   SELECT CAST(999999999999 AS SMALLINT); -- 错误：smallint out of range
    
   -- 正确处理方式：检查数值范围或使用更大范围的类型
   SELECT CASE WHEN 999999999999 BETWEEN -32768 AND 32767 THEN CAST(999999999999 AS SMALLINT) ELSE NULL END;

3. 无效的日期时间格式转换

   -- 错误示例：不符合标准格式的日期字符串转换
   SELECT CAST('2023/10/01' AS DATE); -- 错误：invalid input syntax for type date
    
   -- 正确处理方式：使用合适的日期格式或预处理字符串
   SELECT CAST(to_date('2023/10/01', 'YYYY/MM/DD') AS DATE);

4. JSON 格式错误转换

   -- 错误示例：无效的 JSON 字符串转换为 JSONB
   SELECT CAST('{name: "Alice"}' AS JSONB); -- 错误：invalid input syntax for type json
    
   -- 正确处理方式：确保 JSON 字符串格式正确
   SELECT CAST('{"name": "Alice"}' AS JSONB);

5. 无效的时区转换

   -- 错误示例：使用不存在的时区
   SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Invalid/Timezone'; -- 错误：invalid time zone
    
   -- 错误示例：时区格式不正确
   SELECT CAST('2023-10-01 14:30:00+25' AS TIMESTAMPTZ); -- 错误：time zone displacement out of range
    
   -- 正确处理方式：使用有效的时区标识符
   SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';
   SELECT CAST('2023-10-01 14:30:00+08' AS TIMESTAMPTZ);

错误处理建议

1. 使用条件表达式进行预检查 在进行类型转换前，先使用条件表达式验证数据格式是否符合要求。

2. 使用 TRY_CAST 函数 TRY_CAST 函数在转换失败时返回 NULL 而不是抛出错误。

   SELECT TRY_CAST('invalid' AS INT); -- 返回 NULL 而不是错误

3. 使用正则表达式验证格式 对于字符串到数值或日期的转换，可以先使用正则表达式验证格式。

   -- 验证是否为有效的整数格式
   SELECT CASE WHEN '123abc' ~ '^-?\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;

4. 异常处理 在应用程序层面处理类型转换异常，提供友好的错误提示和备选方案。