数据类型 #

CockroachDB 数据类型
├── 数值类型
│   ├── 整数类型
│   │   ├── INT / INTEGER / BIGINT
│   │   ├── INT2 / SMALLINT
│   │   └── INT4 / INT
│   ├── 浮点类型
│   │   ├── FLOAT / FLOAT8
│   │   └── REAL
│   └── 定点类型
│       └── DECIMAL / NUMERIC
│
├── 字符串类型
│   ├── CHAR / CHARACTER
│   ├── VARCHAR / CHARACTER VARYING
│   ├── TEXT / STRING
│   └── BYTES
│
├── 日期时间类型
│   ├── DATE
│   ├── TIME
│   ├── TIMESTAMP
│   ├── TIMESTAMPTZ
│   └── INTERVAL
│
├── JSON类型
│   ├── JSON
│   └── JSONB
│
├── 数组类型
│   └── ARRAY
│
└── 其他类型
    ├── BOOLEAN / BOOL
    ├── UUID
    ├── ENUM
    ├── BIT
    └── INET

-- 创建表
CREATE TABLE integers (
    id INT PRIMARY KEY DEFAULT unique_rowid(),
    small_col SMALLINT,
    int_col INT,
    big_col BIGINT
);

-- 插入数据
INSERT INTO integers (small_col, int_col, big_col)
VALUES 
    (32767, 2147483647, 9223372036854775807);

-- 查询
SELECT * FROM integers;

-- 使用 unique_rowid()
CREATE TABLE users (
    id BIGINT PRIMARY KEY DEFAULT unique_rowid(),
    name VARCHAR(100)
);

-- 使用序列
CREATE SEQUENCE user_seq;
CREATE TABLE users2 (
    id BIGINT PRIMARY KEY DEFAULT nextval('user_seq'),
    name VARCHAR(100)
);

-- 使用 UUID
CREATE TABLE users3 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100)
);

CREATE TABLE floats (
    id INT PRIMARY KEY,
    float_col FLOAT,
    real_col REAL
);

INSERT INTO floats (id, float_col, real_col)
VALUES 
    (1, 3.14159265358979, 3.14159);

SELECT * FROM floats;

-- 浮点数精度问题
SELECT 0.1::FLOAT + 0.2::FLOAT;  
-- 结果可能不是精确的 0.3

-- 金融计算建议使用 DECIMAL
SELECT 0.1::DECIMAL + 0.2::DECIMAL;
-- 结果: 0.3

-- DECIMAL(precision, scale)
-- precision: 总位数
-- scale: 小数位数

CREATE TABLE decimals (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2),      
    rate DECIMAL(5, 4),        
    amount DECIMAL(20, 6)      
);

INSERT INTO decimals (id, price, rate, amount)
VALUES 
    (1, 99999999.99, 1.2345, 12345678901234.567890);

SELECT * FROM decimals;

-- 账户余额表
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    balance DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
    frozen_amount DECIMAL(20, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 转账事务
BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE user_id = 2;
COMMIT;

-- 查询余额
SELECT user_id, balance FROM accounts;

CREATE TABLE strings (
    id INT PRIMARY KEY,
    char_col CHAR(10),        
    varchar_col VARCHAR(255), 
    text_col TEXT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

INSERT INTO strings (char_col, varchar_col, text_col, name, email)
VALUES 
    ('hello', 'This is a variable length string', 'Long text content...', 'Alice', 'alice@example.com'),
    ('world', 'Another string', 'More text...', 'Bob', 'bob@example.com');

SELECT * FROM strings;

-- CHAR 定长存储，不足补空格
-- CHAR(10) 存储 'hello' 实际存储 'hello     '

-- VARCHAR 变长存储，按实际长度
-- VARCHAR(10) 存储 'hello' 实际存储 'hello'

-- TEXT 无长度限制
-- 推荐使用 TEXT 替代 VARCHAR

-- BYTES 类型存储二进制数据
CREATE TABLE files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100),
    file_size INT,
    file_data BYTES,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入二进制数据
INSERT INTO files (filename, mime_type, file_size, file_data)
VALUES ('test.bin', 'application/octet-stream', 4, b'\x00\x01\x02\x03');

-- 读取二进制数据
SELECT filename, length(file_data) AS size FROM files;

-- 查看支持的排序规则
SHOW COLLATIONS;

-- 指定排序规则
CREATE TABLE collation_test (
    id INT PRIMARY KEY,
    name VARCHAR(100) COLLATE en_US
);

-- 使用特定排序规则查询
SELECT * FROM collation_test 
ORDER BY name COLLATE en_US;

CREATE TABLE dates (
    id INT PRIMARY KEY,
    birth_date DATE NOT NULL,
    hire_date DATE,
    event_date DATE
);

INSERT INTO dates (birth_date, hire_date, event_date)
VALUES 
    ('1990-05-15', '2020-01-01', '2024-12-25'),
    ('1985-08-20', '2018-06-15', '2024-01-01');

-- 日期函数
SELECT 
    birth_date,
    EXTRACT(YEAR FROM birth_date) AS year,
    EXTRACT(MONTH FROM birth_date) AS month,
    EXTRACT(DAY FROM birth_date) AS day,
    birth_date + INTERVAL '1 year' AS next_year
FROM dates;

CREATE TABLE times (
    id INT PRIMARY KEY,
    start_time TIME,
    end_time TIME
);

INSERT INTO times (start_time, end_time)
VALUES 
    ('09:00:00', '18:00:00'),
    ('08:30:00', '17:30:00');

-- 时间函数
SELECT 
    start_time,
    end_time,
    end_time - start_time AS duration
FROM times;

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_name VARCHAR(100) NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO events (event_name, start_time, end_time)
VALUES 
    ('会议', '2024-03-27 10:00:00', '2024-03-27 12:00:00'),
    ('培训', '2024-03-28 14:00:00', '2024-03-28 17:00:00');

-- 时间戳函数
SELECT 
    event_name,
    start_time,
    TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS formatted,
    end_time - start_time AS duration
FROM events;

-- 带时区的时间戳
CREATE TABLE events_tz (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_name VARCHAR(100) NOT NULL,
    event_time TIMESTAMPTZ NOT NULL
);

INSERT INTO events_tz (event_name, event_time)
VALUES ('国际会议', '2024-03-27 10:00:00+08:00');

-- 时区转换
SELECT 
    event_name,
    event_time,
    event_time AT TIME ZONE 'UTC' AS utc_time,
    event_time AT TIME ZONE 'America/New_York' AS ny_time
FROM events_tz;

-- 时间间隔
SELECT 
    INTERVAL '1 year',
    INTERVAL '2 months',
    INTERVAL '3 days',
    INTERVAL '4 hours',
    INTERVAL '5 minutes',
    INTERVAL '6 seconds';

-- 时间计算
SELECT 
    NOW() AS current_time,
    NOW() + INTERVAL '1 day' AS tomorrow,
    NOW() - INTERVAL '1 week' AS last_week,
    NOW() + INTERVAL '1 month' AS next_month;

-- 创建间隔列
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT,
    duration INTERVAL,
    start_time TIMESTAMP DEFAULT NOW()
);

INSERT INTO subscriptions (user_id, duration)
VALUES (1, INTERVAL '1 year');

SELECT 
    user_id,
    start_time,
    duration,
    start_time + duration AS end_time
FROM subscriptions;

CREATE TABLE json_data (
    id INT PRIMARY KEY,
    data JSONB,
    config JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入 JSON 数据
INSERT INTO json_data (id, data, config)
VALUES 
    (1, 
     '{"name": "Alice", "age": 25, "skills": ["PostgreSQL", "CockroachDB"]}',
     '{"theme": "dark", "language": "zh-CN"}'),
    (2, 
     '{"name": "Bob", "age": 30, "address": {"city": "Beijing", "zip": "100000"}}',
     '{"theme": "light", "language": "en-US"}');

SELECT * FROM json_data;

-- 提取 JSON 值
SELECT 
    id,
    data->>'name' AS name,
    data->'age' AS age,
    data->'skills' AS skills
FROM json_data;

-- 提取嵌套数据
SELECT 
    id,
    data->>'name' AS name,
    data->'address'->>'city' AS city,
    data->'address'->>'zip' AS zip
FROM json_data
WHERE data->'address' IS NOT NULL;

-- 提取数组元素
SELECT 
    id,
    data->'skills'->0 AS first_skill,
    jsonb_array_length(data->'skills') AS skills_count
FROM json_data
WHERE jsonb_typeof(data->'skills') = 'array';

-- 设置 JSON 值
UPDATE json_data 
SET data = jsonb_set(data, '{age}', '26')
WHERE id = 1;

-- 添加新字段
UPDATE json_data 
SET data = data || '{"email": "alice@example.com"}'
WHERE id = 1;

-- 删除字段
UPDATE json_data 
SET data = data - 'email'
WHERE id = 1;

-- 追加数组元素
UPDATE json_data 
SET data = jsonb_set(
    data, 
    '{skills}', 
    data->'skills' || '"MySQL"'
)
WHERE id = 1;

-- JSON 包含
SELECT * FROM json_data 
WHERE data @> '{"name": "Alice"}';

-- JSON 路径存在
SELECT * FROM json_data 
WHERE data ? 'address';

-- JSON 搜索
SELECT * FROM json_data 
WHERE data->>'name' = 'Alice';

-- 创建 GIN 索引
CREATE INDEX idx_data ON json_data USING GIN (data);

-- 创建表达式索引
CREATE INDEX idx_data_name ON json_data ((data->>'name'));

-- 使用索引查询
SELECT * FROM json_data WHERE data->>'name' = 'Alice';

-- 创建数组列
CREATE TABLE arrays (
    id INT PRIMARY KEY,
    tags STRING[],
    numbers INT[],
    matrix INT[][]
);

-- 插入数组数据
INSERT INTO arrays (id, tags, numbers, matrix)
VALUES 
    (1, ARRAY['tag1', 'tag2', 'tag3'], ARRAY[1, 2, 3], ARRAY[[1,2], [3,4]]),
    (2, ARRAY['a', 'b'], ARRAY[10, 20, 30], ARRAY[[5,6], [7,8]]);

-- 查询数组
SELECT * FROM arrays;

-- 访问数组元素 (索引从1开始)
SELECT 
    id,
    tags[1] AS first_tag,
    numbers[2] AS second_number
FROM arrays;

-- 数组长度
SELECT 
    id,
    array_length(tags, 1) AS tags_count
FROM arrays;

-- 数组包含
SELECT * FROM arrays 
WHERE tags @> ARRAY['tag1'];

-- 数组元素检查
SELECT * FROM arrays 
WHERE 'tag1' = ANY(tags);

-- 数组连接
SELECT 
    id,
    array_to_string(tags, ', ') AS tags_str
FROM arrays;

CREATE TABLE booleans (
    id INT PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE
);

INSERT INTO booleans (is_active, is_deleted)
VALUES (TRUE, FALSE), (FALSE, TRUE), (true, false);

SELECT 
    id,
    is_active,
    is_deleted,
    is_active IS TRUE AS active_check
FROM booleans;

-- UUID 类型
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

-- 查询
SELECT id, name FROM users;

-- 从字符串转换
SELECT '550e8400-e29b-41d4-a716-446655440000'::UUID;

-- 创建枚举类型
CREATE TYPE status_type AS ENUM ('pending', 'active', 'inactive', 'deleted');

-- 使用枚举类型
CREATE TABLE enums (
    id INT PRIMARY KEY,
    status status_type DEFAULT 'pending',
    priority status_type
);

INSERT INTO enums (status, priority)
VALUES 
    ('active', 'pending'),
    ('inactive', 'active');

-- 查询
SELECT id, status, priority FROM enums;

-- IP地址类型
CREATE TABLE networks (
    id INT PRIMARY KEY,
    ip_addr INET,
    cidr_range CIDR
);

INSERT INTO networks (ip_addr, cidr_range)
VALUES 
    ('192.168.1.1', '192.168.1.0/24'),
    ('10.0.0.1', '10.0.0.0/8');

-- 查询
SELECT 
    ip_addr,
    cidr_range,
    host(ip_addr) AS host,
    netmask(cidr_range) AS netmask
FROM networks;

-- CAST 函数
SELECT 
    CAST('123' AS INT) AS int_val,
    CAST('123.45' AS DECIMAL(10, 2)) AS decimal_val,
    CAST(123 AS VARCHAR) AS char_val,
    CAST('2024-03-27' AS DATE) AS date_val;

-- :: 语法
SELECT 
    '123'::INT AS int_val,
    '123.45'::DECIMAL(10, 2) AS decimal_val,
    123::VARCHAR AS char_val,
    '2024-03-27'::DATE AS date_val;

-- 数值与字符串转换
SELECT '123'::INT + 456;  
SELECT CONCAT(123, 'abc');  

-- 日期转换
SELECT DATE('2024-03-27 10:30:00');
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');