PostgreSQL如何按level字段自定义优先级实现多条件排序

实现方案 #

1. 调整表结构替换urgent字段为level #

首先执行表结构修改SQL，完成字段替换，可按需添加约束避免非法level值写入：

-- 新增level字段，默认值可按需调整
ALTER TABLE {accountId}.{tableAlertsName} ADD COLUMN level varchar NOT NULL DEFAULT 'info';
-- 可选：如果需要保留历史数据的优先级映射，根据原urgent字段赋值
UPDATE {accountId}.{tableAlertsName} SET level = 'error' WHERE urgent = true;
-- 删除原urgent字段
ALTER TABLE {accountId}.{tableAlertsName} DROP COLUMN urgent;
-- 可选：添加CHECK约束限制level的合法取值
ALTER TABLE {accountId}.{tableAlertsName} ADD CONSTRAINT chk_alerts_level CHECK (level IN ('info', 'warning', 'error'));

2. 修改视图实现自定义排序 #

通过CASE表达式给不同level分配排序权重，即可实现你需要的排序规则：

CREATE OR REPLACE VIEW {accountId}.pending_alerts AS
    SELECT id, ts, level, origin, message 
    FROM {accountId}.{tableAlertsName}
    WHERE ts_read IS NULL 
    ORDER BY 
        -- warning、error优先级最高，排在最前面
        CASE WHEN level IN ('warning', 'error') THEN 1 ELSE 2 END ASC,
        ts ASC;

如果你需要进一步区分error和warning的优先级（error排在warning前），可以调整CASE逻辑如下：

CASE level 
    WHEN 'error' THEN 1 
    WHEN 'warning' THEN 2 
    ELSE 3 
END ASC

内容的提问来源于stack exchange，提问作者Thomas