PostgreSQL - PopSQL

PopSQL is a unified SQL collaboration workspace that connects everyone in the data analysis process so you can obtain better insights by asking the right questions, together.

PopSQL

个人习惯，数据库尽量规范化，不要提早做优化。性能出了问题先尝试缓存、读写分离等等，最后再考虑反规范化。

Docker 启动

docker run -p 3306:3306  --name mysql -e MYSQL_ROOT_PASSWORD=password -d mysql

关联统计

SELECT *,(select count(Stories.id) from Stories where project_id=projects.id)storyCount FROM `projects` WHERE `projects`.`deleted_at` IS NULL LIMIT 10

JSON的一些操作

使用全文搜索JSONB

SELECT mark.note.comment, id, content
FROM mark.note
WHERE comment::text similar to '%consumers%'
   or content similar to '%consumers%'
order by updated_at DESC;

自动生成uuid

CREATE EXTENSION pgcrypto;

雪花ID SQL函数

生成序列和创建函数要分步执行

-- 创建序列来替代表
CREATE SEQUENCE IF NOT EXISTS snowflake_sequence;

-- 创建雪花ID生成函数
CREATE OR REPLACE FUNCTION generate_snowflake_id(node_id int)
RETURNS bigint AS $$
DECLARE
    -- 常量定义
    epoch bigint := 1640995200000;  -- 2022-01-01 作为纪元
    timestamp_bits int := 41;
    node_id_bits int := 10;
    sequence_bits int := 12;
    
    -- 计算最大值
    max_node_id bigint := (1 << node_id_bits) - 1;
    max_sequence bigint := (1 << sequence_bits) - 1;
    
    -- 变量定义
    timestamp bigint;
    sequence bigint;
    last_timestamp bigint := 0;
BEGIN
    -- 检查节点ID是否有效
    IF node_id < 0 OR node_id > max_node_id THEN
        RAISE EXCEPTION 'Invalid node ID: %', node_id;
    END IF;

    -- 获取当前时间戳（毫秒）
    timestamp := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint - epoch;
    
    -- 如果时间戳回退，抛出异常
    IF timestamp < last_timestamp THEN
        RAISE EXCEPTION 'Clock moved backwards. Refusing to generate id for % milliseconds', last_timestamp - timestamp;
    END IF;
    
    -- 如果是同一毫秒，增加序列号
    IF timestamp = last_timestamp THEN
        sequence := (nextval('snowflake_sequence') - 1) & max_sequence;
        -- 如果序列号溢出，等待下一毫秒
        IF sequence = 0 THEN
            timestamp := timestamp + 1;
        END IF;
    ELSE
        -- 不同毫秒，重置序列号
        sequence := 0;
        last_timestamp := timestamp;
        -- 重置序列
        PERFORM setval('snowflake_sequence', 1, false);
    END IF;
    
    -- 生成并返回雪花ID
    RETURN (
        (timestamp << (node_id_bits + sequence_bits)) |
        (node_id << sequence_bits) |
        sequence
    );
END;
$$ LANGUAGE plpgsql;

使用方法：id_generator()

随机查找

CREATE
OR REPLACE FUNCTION npmvs.lucky_packages(num integer) RETURNS SETOF npmvs.package LANGUAGE sql STABLE AS $ function $
select
  *
from
  npmvs.package
order by
  random()
limit
  num $ function $

OR

CREATE
OR REPLACE FUNCTION npmvs.lucky_tags(num integer) RETURNS SETOF npmvs.tag LANGUAGE sql STABLE AS $ function $
select
  id,
  name
from
  npmvs.tag
order by
  random()
limit
  5 $ function $