Postgres生成测试数据的脚本

-- 创建通用测试表
CREATE TABLE IF NOT EXISTS test_table (
    col1 VARCHAR(32),    
    col2 VARCHAR(2),     
    col3 VARCHAR(32),    
    col4 VARCHAR(4),     
    col5 NUMERIC(5,1),   
    col6 NUMERIC(5,1),   
    col7 DATE,           
    col8 DATE,           
    col9 TIMESTAMP,      
    col10 VARCHAR(32),   
    col11 TIMESTAMP,     
    col12 VARCHAR(32),     -- 新增列   
    col13 INTEGER,       -- 整数类型
    col14 BOOLEAN,       -- 布尔类型
    col15 TEXT          -- 文本类型
);

-- 生成测试数据
INSERT INTO test_table
SELECT 
    -- VARCHAR(32) 字段：生成 'Value-' + 序号
    'Value-' || i AS col1,
    -- VARCHAR(2) 字段：生成 2个字母
    SUBSTRING(MD5(i::text), 1, 2) AS col2,
    -- VARCHAR(32) 字段
    'Value-' || (i * 2) AS col3,
    -- VARCHAR(4) 字段
    LPAD(i::text, 4, '0') AS col4,
    -- NUMERIC(5,1) 字段：生成0.0-999.9的随机数
    ROUND(CAST(random() * 999.9 AS NUMERIC), 1) AS col5,
    -- NUMERIC(5,1) 字段
    ROUND(CAST(random() * 999.9 AS NUMERIC), 1) AS col6,
    -- DATE 字段：生成过去一年内的随机日期
    CURRENT_DATE - (random() * 365)::INTEGER AS col7,
    -- DATE 字段
    CURRENT_DATE - (random() * 365)::INTEGER AS col8,
    -- TIMESTAMP 字段：生成过去一年内的随机时间戳
    CURRENT_TIMESTAMP - (random() * 365 * 24 * 60 * 60)::INTEGER * INTERVAL '1 second' AS col9,
    -- VARCHAR(32) 字段
    'Value-' || (i * 3) AS col10,
    -- TIMESTAMP 字段
    CURRENT_TIMESTAMP - (random() * 365 * 24 * 60 * 60)::INTEGER * INTERVAL '1 second' AS col11,
    -- VARCHAR(32) 字段
    'Value-' || (i * 4) AS col12,
    -- INTEGER 字段，生成0到1,000,000之间的随机整数
    (random() * 1000000)::INTEGER AS col13,  
    -- 随机生成布尔值
    CASE WHEN random() < 0.5 THEN TRUE ELSE FALSE END AS col14,
    -- 生成不同长度的文本内容
    CASE (i % 3)
        WHEN 0 THEN 'This is a short text for row ' || i
        WHEN 1 THEN 'This is a medium length text that contains more details for row ' || i || '. It includes additional information.'
        ELSE 'This is a long text for row ' || i || '. It contains multiple sentences and simulates a detailed description or comment. ' ||
             'It might include various information like specifications, notes, or other relevant details that need to be stored in the database. ' ||
             'The length of this text varies to simulate real-world scenarios where text fields contain varying amounts of information.'
    END AS col15
FROM generate_series(1, 1000) i;