数据类型 #

TimescaleDB数据类型分类：

基本类型
├── 数值类型
├── 字符串类型
├── 布尔类型
└── 时间类型

复合类型
├── 数组类型
├── JSON类型
├── 复合类型
└── 范围类型

特殊类型
├── UUID
├── 几何类型
├── 网络地址类型
└── 位串类型

时序数据常用类型：

时间列（必需）
├── TIMESTAMPTZ（推荐）
├── TIMESTAMP
└── DATE

数值列
├── DOUBLE PRECISION
├── INTEGER
├── BIGINT
└── DECIMAL

标签列
├── INTEGER
├── VARCHAR
├── TEXT
└── UUID

-- SMALLINT：小整数（-32768 到 32767）
CREATE TABLE example (
    small_int SMALLINT
);

INSERT INTO example VALUES (100);
INSERT INTO example VALUES (-32768);  -- 最小值
INSERT INTO example VALUES (32767);   -- 最大值

-- INTEGER：整数（-2147483648 到 2147483647）
ALTER TABLE example ADD COLUMN int_val INTEGER;

INSERT INTO example (int_val) VALUES (1000000);

-- BIGINT：大整数（-9223372036854775808 到 9223372036854775807）
ALTER TABLE example ADD COLUMN big_int BIGINT;

INSERT INTO example (big_int) VALUES (9223372036854775807);

整数类型对比：

类型         存储空间    范围
─────────────────────────────────────────
SMALLINT     2字节      -32,768 到 32,767
INTEGER      4字节      -2,147,483,648 到 2,147,483,647
BIGINT       8字节      -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807

-- REAL：单精度浮点数
ALTER TABLE example ADD COLUMN real_val REAL;

INSERT INTO example (real_val) VALUES (3.14159);

-- DOUBLE PRECISION：双精度浮点数（时序数据推荐）
ALTER TABLE example ADD COLUMN double_val DOUBLE PRECISION;

INSERT INTO example (double_val) VALUES (3.141592653589793);

-- 时序数据示例
CREATE TABLE sensor_readings (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION
);

-- DECIMAL/NUMERIC：精确小数
ALTER TABLE example ADD COLUMN decimal_val DECIMAL(10, 2);

INSERT INTO example (decimal_val) VALUES (12345.67);

-- 金融数据示例
CREATE TABLE financial_data (
    time TIMESTAMPTZ NOT NULL,
    symbol VARCHAR(10),
    price DECIMAL(18, 6),
    volume BIGINT
);

INSERT INTO financial_data VALUES
    (NOW(), 'AAPL', 175.50, 1000000);

-- SERIAL：自增整数
CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- BIGSERIAL：自增大整数
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    time TIMESTAMPTZ NOT NULL,
    data JSONB
);

-- 插入数据（id自动生成）
INSERT INTO devices (name) VALUES ('Sensor-1');
INSERT INTO devices (name) VALUES ('Sensor-2');

SELECT * FROM devices;
-- id | name
-- ---+---------
--  1 | Sensor-1
--  2 | Sensor-2

-- TIMESTAMPTZ：带时区的时间戳（TimescaleDB推荐）
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    value DOUBLE PRECISION
);

-- 插入时间数据
INSERT INTO sensor_data VALUES
    ('2024-01-01 00:00:00+00', 1, 25.5),
    ('2024-01-01 08:00:00+08', 1, 26.0),
    (NOW(), 1, 25.8);

-- 查询时间
SELECT time, sensor_id, value FROM sensor_data;

-- 时区转换
SELECT time AT TIME ZONE 'UTC' AS utc_time,
       time AT TIME ZONE 'Asia/Shanghai' AS local_time
FROM sensor_data;

-- TIMESTAMP：不带时区的时间戳
CREATE TABLE events (
    time TIMESTAMP NOT NULL,
    event_name VARCHAR(100)
);

-- 插入数据
INSERT INTO events VALUES
    ('2024-01-01 10:00:00', 'Event-1'),
    ('2024-01-01 11:30:00', 'Event-2');

-- 注意：TIMESTAMP不存储时区信息
-- 推荐使用TIMESTAMPTZ

-- DATE：日期
CREATE TABLE daily_stats (
    date DATE PRIMARY KEY,
    total_readings BIGINT,
    avg_temperature DOUBLE PRECISION
);

INSERT INTO daily_stats VALUES
    ('2024-01-01', 1000, 25.5),
    ('2024-01-02', 1200, 26.0);

-- TIME：时间
ALTER TABLE daily_stats ADD COLUMN peak_time TIME;

UPDATE daily_stats SET peak_time = '14:30:00'
WHERE date = '2024-01-01';

-- INTERVAL：时间间隔
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '2 hours';
SELECT NOW() + INTERVAL '1 month 2 days 3 hours';

-- 时间间隔计算
SELECT 
    time,
    time + INTERVAL '1 hour' AS next_hour,
    time - INTERVAL '30 minutes' AS prev_30min
FROM sensor_data;

-- 时间差计算
SELECT 
    time,
    NOW() - time AS time_ago
FROM sensor_data;

-- 当前时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

-- 时间提取
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT EXTRACT(MINUTE FROM NOW());
SELECT EXTRACT(SECOND FROM NOW());

-- 时间截断
SELECT DATE_TRUNC('day', NOW());
SELECT DATE_TRUNC('hour', NOW());
SELECT DATE_TRUNC('month', NOW());

-- 时间格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY');

-- 时间桶（TimescaleDB特有）
SELECT time_bucket('1 hour', NOW());
SELECT time_bucket('1 day', NOW());
SELECT time_bucket('15 minutes', NOW());

-- VARCHAR(n)：变长字符串
CREATE TABLE sensors (
    sensor_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(200)
);

INSERT INTO sensors VALUES
    (1, 'Temperature Sensor', 'Building A, Floor 1');

-- CHAR(n)：定长字符串
ALTER TABLE sensors ADD COLUMN code CHAR(10);

UPDATE sensors SET code = 'TEMP001' WHERE sensor_id = 1;
-- code会被填充为 'TEMP001   '

-- TEXT：无限长字符串
CREATE TABLE logs (
    time TIMESTAMPTZ NOT NULL,
    message TEXT,
    details TEXT
);

INSERT INTO logs VALUES
    (NOW(), 'System started', 'Detailed log message...'),
    (NOW(), 'Error occurred', 'Error details...');

-- TEXT和VARCHAR性能差异很小
-- 推荐使用TEXT或VARCHAR

-- 字符串连接
SELECT 'Temperature: ' || temperature || '°C'
FROM sensor_data;

-- 大小写转换
SELECT UPPER(name) FROM sensors;
SELECT LOWER(name) FROM sensors;

-- 字符串长度
SELECT LENGTH(name) FROM sensors;

-- 子字符串
SELECT SUBSTRING(name, 1, 10) FROM sensors;
SELECT LEFT(name, 5) FROM sensors;
SELECT RIGHT(name, 5) FROM sensors;

-- 字符串查找
SELECT POSITION('Sensor' IN name) FROM sensors;

-- 字符串替换
SELECT REPLACE(name, 'Sensor', 'Device') FROM sensors;

-- 模式匹配
SELECT * FROM sensors WHERE name LIKE '%Temp%';
SELECT * FROM sensors WHERE name ILIKE '%temp%';  -- 不区分大小写

-- BOOLEAN：布尔类型
CREATE TABLE alerts (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    is_active BOOLEAN,
    is_critical BOOLEAN
);

INSERT INTO alerts VALUES
    (NOW(), 1, TRUE, FALSE),
    (NOW(), 2, true, true),
    (NOW(), 3, 't', 'f'),
    (NOW(), 4, 'yes', 'no');

-- 查询布尔值
SELECT * FROM alerts WHERE is_active = TRUE;
SELECT * FROM alerts WHERE is_active;  -- 简写
SELECT * FROM alerts WHERE NOT is_critical;

布尔值表示：

TRUE值    FALSE值
───────────────────────
TRUE      FALSE
true      false
't'       'f'
'yes'     'no'
'y'       'n'
'1'       '0'
1         0

-- 一维数组
CREATE TABLE sensor_arrays (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    readings DOUBLE PRECISION[]  -- 双精度数组
);

INSERT INTO sensor_arrays VALUES
    (NOW(), 1, ARRAY[25.5, 26.0, 25.8, 26.2]),
    (NOW(), 2, '{27.1, 27.3, 27.0, 27.2}');

-- 多维数组
CREATE TABLE matrix_data (
    time TIMESTAMPTZ NOT NULL,
    matrix INTEGER[][]
);

INSERT INTO matrix_data VALUES
    (NOW(), '{{1,2,3},{4,5,6},{7,8,9}}');

-- 访问数组元素（索引从1开始）
SELECT readings[1] FROM sensor_arrays;

-- 访问多个元素
SELECT readings[1:3] FROM sensor_arrays;

-- 数组长度
SELECT array_length(readings, 1) FROM sensor_arrays;

-- 数组元素个数
SELECT cardinality(readings) FROM sensor_arrays;

-- 数组连接
SELECT ARRAY[1,2] || ARRAY[3,4];
SELECT ARRAY[1,2] || 3;

-- 数组函数
SELECT array_append(ARRAY[1,2], 3);
SELECT array_prepend(0, ARRAY[1,2]);
SELECT array_remove(ARRAY[1,2,3], 2);
SELECT array_replace(ARRAY[1,2,3], 2, 5);

-- 数组聚合
SELECT sensor_id, array_agg(temperature)
FROM sensor_data
GROUP BY sensor_id;

-- 检查元素是否存在
SELECT * FROM sensor_arrays
WHERE 25.5 = ANY(readings);

-- 检查所有元素
SELECT * FROM sensor_arrays
WHERE 20.0 < ALL(readings);

-- 数组包含
SELECT * FROM sensor_arrays
WHERE readings @> ARRAY[25.5];

-- 数组重叠
SELECT * FROM sensor_arrays
WHERE readings && ARRAY[25.5, 30.0];

-- JSON：文本存储
CREATE TABLE events_json (
    time TIMESTAMPTZ NOT NULL,
    data JSON
);

-- JSONB：二进制存储（推荐）
CREATE TABLE events_jsonb (
    time TIMESTAMPTZ NOT NULL,
    data JSONB
);

-- 插入JSON数据
INSERT INTO events_jsonb VALUES
    (NOW(), '{"sensor_id": 1, "temperature": 25.5, "location": "Building A"}'),
    (NOW(), '{"sensor_id": 2, "humidity": 60.0, "status": "active"}');

-- JSONB优势
-- 1. 更快的查询处理
-- 2. 支持索引
-- 3. 自动去除空白和重复键

-- 提取值
SELECT data->>'sensor_id' FROM events_jsonb;
SELECT data->'temperature' FROM events_jsonb;

-- 提取嵌套值
INSERT INTO events_jsonb VALUES
    (NOW(), '{"sensor": {"id": 1, "name": "Temp-1"}, "reading": 25.5}');

SELECT data->'sensor'->>'name' FROM events_jsonb;

-- 提取数组元素
INSERT INTO events_jsonb VALUES
    (NOW(), '{"readings": [25.5, 26.0, 25.8]}');

SELECT data->'readings'->0 FROM events_jsonb;

-- JSON路径查询
SELECT jsonb_path_query(data, '$.sensor.name') FROM events_jsonb;

-- 创建JSON
SELECT to_json('Hello'::TEXT);
SELECT json_build_object('name', 'John', 'age', 30);
SELECT json_build_array(1, 2, 3, 4);

-- JSON聚合
SELECT sensor_id, json_agg(temperature)
FROM sensor_data
GROUP BY sensor_id;

SELECT sensor_id, json_object_agg('temp', temperature)
FROM sensor_data
GROUP BY sensor_id;

-- JSON修改
UPDATE events_jsonb
SET data = data || '{"status": "processed"}'::jsonb
WHERE time > NOW() - INTERVAL '1 hour';

-- 删除键
UPDATE events_jsonb
SET data = data - 'status';

-- JSONB包含检查
SELECT * FROM events_jsonb
WHERE data @> '{"sensor_id": 1}'::jsonb;

-- 创建GIN索引
CREATE INDEX idx_data_gin ON events_jsonb USING GIN (data);

-- 创建路径索引
CREATE INDEX idx_data_sensor_id ON events_jsonb ((data->>'sensor_id'));

-- 使用索引查询
SELECT * FROM events_jsonb
WHERE data @> '{"sensor_id": 1}';

-- 创建UUID列
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE devices (
    device_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据
INSERT INTO devices (name) VALUES ('Device-1');
INSERT INTO devices (name) VALUES ('Device-2');

SELECT * FROM devices;
-- device_id                             | name     | created_at
-- --------------------------------------+----------+-------------------------
-- a1b2c3d4-e5f6-7890-abcd-ef1234567890 | Device-1 | 2024-01-01 10:00:00+00

-- 生成UUID
SELECT uuid_generate_v4();  -- 随机UUID
SELECT uuid_generate_v1();  -- 基于时间戳

-- 字符串转UUID
SELECT 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'::UUID;

-- 创建枚举类型
CREATE TYPE sensor_status AS ENUM (
    'active',
    'inactive',
    'maintenance',
    'error'
);

-- 使用枚举类型
CREATE TABLE sensors (
    sensor_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    status sensor_status DEFAULT 'active'
);

INSERT INTO sensors VALUES
    (1, 'Sensor-1', 'active'),
    (2, 'Sensor-2', 'maintenance');

-- 查询枚举值
SELECT * FROM sensors WHERE status = 'active';

-- 内置范围类型
-- int4range, int8range, numrange, tsrange, tstzrange, daterange

CREATE TABLE maintenance_windows (
    sensor_id INTEGER,
    window TSTZRANGE
);

INSERT INTO maintenance_windows VALUES
    (1, '[2024-01-01 00:00:00+00, 2024-01-01 02:00:00+00)');

-- 范围查询
SELECT * FROM maintenance_windows
WHERE window @> NOW();

-- 范围重叠
SELECT * FROM maintenance_windows
WHERE window && '[2024-01-01 01:00:00+00, 2024-01-01 03:00:00+00)';

-- INET：IP地址
CREATE TABLE network_devices (
    device_id INTEGER PRIMARY KEY,
    ip_address INET,
    mac_address MACADDR
);

INSERT INTO network_devices VALUES
    (1, '192.168.1.100', '08:00:2b:01:02:03'),
    (2, '192.168.1.101/24', '08:00:2b:01:02:04');

-- CIDR：IP网络
ALTER TABLE network_devices ADD COLUMN network CIDR;

UPDATE network_devices SET network = '192.168.1.0/24';

-- 使用CAST
SELECT CAST('123' AS INTEGER);
SELECT CAST('2024-01-01' AS DATE);
SELECT CAST(123.45 AS VARCHAR);

-- 使用::操作符
SELECT '123'::INTEGER;
SELECT '2024-01-01'::DATE;
SELECT '2024-01-01 10:00:00'::TIMESTAMPTZ;

-- JSON转换
SELECT '{"a": 1}'::JSONB;
SELECT to_json(ROW('John', 30));

-- PostgreSQL自动转换
SELECT '123' + 456;  -- 字符串自动转为整数

-- 时间转换
SELECT DATE '2024-01-01';
SELECT TIMESTAMP '2024-01-01 10:00:00';
SELECT TIMESTAMPTZ '2024-01-01 10:00:00+08';