Supabase数据类型 #

-- 整数类型示例
CREATE TABLE numbers (
    id BIGSERIAL PRIMARY KEY,
    small_num SMALLINT,
    normal_num INTEGER,
    big_num BIGINT,
    auto_id SERIAL
);

-- 插入数据
INSERT INTO numbers (small_num, normal_num, big_num)
VALUES (100, 100000, 9999999999999);

-- 浮点类型示例
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    weight REAL,
    rating DOUBLE PRECISION
);

-- 插入数据
INSERT INTO products (name, price, weight, rating)
VALUES ('Widget', 99.99, 1.5, 4.567890123456789);

-- 货币类型
CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    amount MONEY NOT NULL,
    currency CHAR(3) DEFAULT 'USD'
);

-- 插入数据
INSERT INTO transactions (amount) VALUES (1234.56);
-- 结果: $1,234.56

-- 字符串类型示例
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    bio TEXT
);

-- 插入数据
INSERT INTO users (country_code, username, bio)
VALUES ('US', 'john_doe', 'A long biography...');

-- 字符串函数
SELECT 
    UPPER('hello') as upper,           -- HELLO
    LOWER('HELLO') as lower,           -- hello
    LENGTH('hello') as length,         -- 5
    CONCAT('Hello', ' ', 'World'),     -- Hello World
    SUBSTRING('Hello World', 1, 5),    -- Hello
    REPLACE('Hello', 'l', 'L'),        -- HeLLo
    TRIM('  hello  '),                  -- hello
    LEFT('Hello', 3),                  -- Hel
    RIGHT('Hello', 3),                 -- llo
    SPLIT_PART('a,b,c', ',', 2);       -- b

-- 布尔类型
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    is_priority BOOLEAN DEFAULT FALSE
);

-- 插入数据
INSERT INTO tasks (title, completed, is_priority)
VALUES ('Task 1', true, false),
       ('Task 2', false, true),
       ('Task 3', 't', 'f'),  -- 也可以用 't'/'f'
       ('Task 4', 'yes', 'no'); -- 或 'yes'/'no'

-- 查询
SELECT * FROM tasks WHERE completed = true;
SELECT * FROM tasks WHERE NOT completed;

-- 日期时间类型
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIMETZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    duration INTERVAL
);

-- 插入数据
INSERT INTO events (event_date, start_time, end_time, duration)
VALUES (
    '2024-01-15',
    '09:00:00',
    '17:00:00+08:00',
    '8 hours'
);

-- 当前时间
SELECT NOW();                    -- 2024-01-15 10:30:00+08
SELECT CURRENT_DATE;             -- 2024-01-15
SELECT CURRENT_TIME;             -- 10:30:00+08
SELECT CURRENT_TIMESTAMP;        -- 2024-01-15 10:30:00+08

-- 日期计算
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 week';
SELECT NOW() + INTERVAL '1 month 2 days 3 hours';

-- 日期提取
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT EXTRACT(DOW FROM NOW());  -- 星期几 (0-6)

-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');
SELECT TO_CHAR(NOW(), 'HH24:MI:SS');

-- 日期截断
SELECT DATE_TRUNC('month', NOW());  -- 月初
SELECT DATE_TRUNC('week', NOW());   -- 周初
SELECT DATE_TRUNC('day', NOW());    -- 当天零点

-- 年龄计算
SELECT AGE(NOW(), '1990-01-01');
SELECT AGE('1990-01-01');

-- 启用uuid-ossp扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UUID表
CREATE TABLE sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ
);

-- 插入数据
INSERT INTO sessions (user_id, expires_at)
VALUES (1, NOW() + INTERVAL '24 hours');

-- UUID函数
SELECT uuid_generate_v4();  -- 随机UUID
SELECT uuid_generate_v1();  -- 基于时间戳的UUID

SERIAL (自增整数)
├── 优点: 紧凑、高效
├── 缺点: 可预测、迁移复杂
└── 适合: 内部系统、小规模应用

UUID
├── 优点: 全局唯一、不可预测、易于迁移
├── 缺点: 占用空间大、索引效率稍低
└── 适合: 分布式系统、公开API

-- 推荐使用JSONB
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB DEFAULT '{}'::jsonb,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- 插入数据
INSERT INTO products (name, attributes, metadata)
VALUES (
    'Laptop',
    '{"color": "silver", "weight": 1.5, "specs": {"cpu": "M1", "ram": 16}}',
    '{"views": 100, "tags": ["electronics", "computer"]}'
);

-- 提取值
SELECT attributes->>'color' FROM products;           -- silver (文本)
SELECT attributes->'specs'->>'cpu' FROM products;    -- M1

-- 提取数组元素
SELECT metadata->'tags'->0 FROM products;            -- "electronics"

-- 条件查询
SELECT * FROM products WHERE attributes->>'color' = 'silver';

-- 包含查询
SELECT * FROM products WHERE attributes @> '{"color": "silver"}';

-- 键存在检查
SELECT * FROM products WHERE attributes ? 'color';

-- 任意键存在
SELECT * FROM products WHERE attributes ?| array['color', 'size'];

-- 所有键存在
SELECT * FROM products WHERE attributes ?& array['color', 'weight'];

-- 更新值
UPDATE products 
SET attributes = jsonb_set(attributes, '{color}', '"gold"');

-- 添加键值
UPDATE products 
SET attributes = attributes || '{"brand": "Apple"}';

-- 删除键
UPDATE products 
SET attributes = attributes - 'weight';

-- 删除嵌套键
UPDATE products 
SET attributes = attributes #- '{specs,ram}';

-- 批量更新
UPDATE products 
SET attributes = attributes || '{"new_key": "new_value"}'::jsonb;

-- 获取所有键
SELECT jsonb_object_keys(attributes) FROM products;

-- 获取所有值
SELECT jsonb_each(attributes) FROM products;

-- 展开数组
SELECT jsonb_array_elements(metadata->'tags') FROM products;

-- 合并JSON
SELECT '{"a": 1}'::jsonb || '{"b": 2}'::jsonb;

-- JSON路径查询
SELECT jsonb_path_query(attributes, '$.specs.cpu') FROM products;

-- GIN索引
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 路径索引
CREATE INDEX idx_products_color ON products USING GIN (attributes jsonb_path_ops);

-- 使用索引的查询
SELECT * FROM products WHERE attributes @> '{"color": "silver"}';

-- 数组类型
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',
    ratings INTEGER[] DEFAULT '{}',
    coordinates NUMERIC[]
);

-- 插入数据
INSERT INTO posts (title, tags, ratings, coordinates)
VALUES (
    'My Post',
    ARRAY['tech', 'programming', 'javascript'],
    ARRAY[5, 4, 5, 3, 5],
    ARRAY[40.7128, -74.0060]
);

-- 或使用字符串语法
INSERT INTO posts (title, tags)
VALUES ('Another Post', '{news,world}');

-- 访问元素 (索引从1开始)
SELECT tags[1] FROM posts;                    -- tech

-- 数组切片
SELECT tags[1:2] FROM posts;                  -- {tech,programming}

-- 数组长度
SELECT array_length(tags, 1) FROM posts;      -- 3

-- 包含元素
SELECT * FROM posts WHERE 'tech' = ANY(tags);

-- 包含所有
SELECT * FROM posts WHERE tags @> ARRAY['tech'];

-- 包含任意
SELECT * FROM posts WHERE tags && ARRAY['tech', 'news'];

-- 重叠
SELECT * FROM posts WHERE tags && ARRAY['tech', 'sports'];

-- 追加元素
UPDATE posts SET tags = array_append(tags, 'new-tag');

-- 前置元素
UPDATE posts SET tags = array_prepend('first-tag', tags);

-- 删除元素
UPDATE posts SET tags = array_remove(tags, 'tech');

-- 替换元素
UPDATE posts SET tags = array_replace(tags, 'tech', 'technology');

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

-- 展开数组
SELECT unnest(tags) FROM posts;

-- 聚合为数组
SELECT array_agg(name) FROM users;

-- GIN索引
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- 使用索引的查询
SELECT * FROM posts WHERE tags @> ARRAY['tech'];

-- 创建枚举类型
CREATE TYPE status_type AS ENUM (
    'draft',
    'published',
    'archived',
    'deleted'
);

-- 使用枚举
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status status_type DEFAULT 'draft'
);

-- 插入数据
INSERT INTO articles (title, status)
VALUES ('My Article', 'published');

-- 查询
SELECT * FROM articles WHERE status = 'published';

-- 添加枚举值
ALTER TYPE status_type ADD VALUE 'review' AFTER 'draft';

-- 重命名枚举值 (PostgreSQL 12+不支持直接重命名)
-- 需要创建新枚举类型并迁移数据

-- 查看枚举值
SELECT enum_range(NULL::status_type);

-- 网络地址类型
CREATE TABLE servers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    ip_address INET,
    cidr_block CIDR,
    mac_address MACADDR
);

-- 插入数据
INSERT INTO servers (name, ip_address, cidr_block, mac_address)
VALUES (
    'Web Server',
    '192.168.1.100',
    '192.168.1.0/24',
    '08:00:2b:01:02:03'
);

-- 查询
SELECT * FROM servers WHERE ip_address << '192.168.1.0/24';

-- 二进制数据
CREATE TABLE files (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    data BYTEA
);

-- 插入数据
INSERT INTO files (name, data)
VALUES ('test.bin', '\x48656c6c6f');

-- XML数据
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content XML
);

INSERT INTO documents (content)
VALUES ('<root><item>Hello</item></root>');

-- 范围类型
CREATE TABLE reservations (
    id BIGSERIAL PRIMARY KEY,
    room_number INTEGER,
    during TSRANGE
);

INSERT INTO reservations (room_number, during)
VALUES (101, '[2024-01-15 14:00, 2024-01-15 16:00)');

-- 查询重叠
SELECT * FROM reservations
WHERE during && TSRANGE('[2024-01-15 15:00, 2024-01-15 17:00)');

-- 使用 ::type 语法
SELECT '123'::INTEGER;
SELECT '2024-01-15'::DATE;
SELECT '{"a": 1}'::JSONB;

-- 使用 CAST 函数
SELECT CAST('123' AS INTEGER);
SELECT CAST('2024-01-15' AS DATE);

-- PostgreSQL会自动进行某些转换
SELECT 1 + '2';  -- 结果: 3 (字符串自动转数字)

-- 但某些情况需要显式转换
SELECT '123' || 456;  -- 错误
SELECT '123' || 456::TEXT;  -- 正确: '123456'