• [ ] 常用操作示例

    查询一段时间内的数据

    1. SELECT count(1) FROM picture
    2. WHERE create_date between (SELECT current_timestamp - interval '12 month') 
    3. and current_timestamp;

    按月统计

    1. SELECT to_char(create_date, 'yyyy-MM') as month, count(*) FROM picture
    2. WHERE create_date between (SELECT current_timestamp - interval '12 month') and current_timestamp 
    3. GROUP BY month;

    函数

    1. explain analyze \copy (
    2. select  
    3. (random()::numeric(7,6)*1000000)::integer as user_id, 
    4. round((random()*100)::numeric, 2) as pay_money, 
    5. ('2020-01-01'::date + (random()*364)::integer) as create_date 
    6. from generate_series(1, 10000000)) to '/home/yanjing/database/pgsql/data.txt';
    8. INSERT INTO native_emp (
    9.   level,
    10.   postal_code,
    11.   create_time
    12. ) SELECT RANDOM()*6, (((RANDOM()::NUMERIC(5, 4))*10000)::INTEGER)::TEXT, g 
    13. FROM generate_series('2020-01-01 00:00'::timestamp, NOW(), '15 seconds') as g;