PostgreSQL：如何实现用户名与数据库匹配并按需显示NULL的查询？

原SQL的关键错误 #

1. 逻辑运算优先级混乱：AND比OR优先级高，原WHERE条件等价于(pr.rolname like ''||pd.datname||'_suffix') OR (pr.rolname like '%_suffix' and pd.datname not in (...))，导致postgres相关行未被过滤，同时漏掉了不匹配的记录。
2. 连接方式错误：CROSS JOIN是笛卡尔积，会强制生成所有角色与数据库的组合，无法实现“无匹配时显示NULL”的需求，必须用FULL OUTER JOIN保留两边不匹配的行。
3. CASE语句误用：原CASE写在WHERE子句中试图赋值，这是逻辑错误——WHERE用于过滤行，设置列值需要放在SELECT子句中。
4. 空值处理错误：用字符串'NULL'代替SQL原生的NULL关键字，导致空值判断失效。

正确SQL实现 #

SELECT
    -- 匹配数据库的角色显示对应库名，否则显示NULL
    CASE WHEN pr.rolname = pd.datname || '_suffix' THEN pd.datname ELSE NULL END AS DB_NAME,
    -- 分场景处理角色显示：匹配库的显示角色名；无匹配库的_suffix角色显示自身；其余显示NULL
    CASE
        WHEN pr.rolname = pd.datname || '_suffix' THEN pr.rolname
        WHEN pr.rolname LIKE '%_suffix' AND NOT EXISTS (
            SELECT 1 FROM pg_database pd2 
            WHERE pd2.datname || '_suffix' = pr.rolname
              AND pd2.datname NOT IN ('postgres', 'template0', 'template1')
        ) THEN pr.rolname
        ELSE NULL
    END AS DB_ROLE
FROM
    pg_roles pr
FULL OUTER JOIN
    pg_database pd ON pr.rolname = pd.datname || '_suffix'
WHERE
    -- 排除系统库，同时仅保留_suffix结尾的角色和非系统库
    (pd.datname IS NULL OR pd.datname NOT IN ('postgres', 'template0', 'template1'))
    AND (pr.rolname IS NULL OR pr.rolname LIKE '%_suffix')
-- 过滤掉两边都为空的无效行
AND NOT (pd.datname IS NULL AND pr.rolname IS NULL)
ORDER BY DB_NAME NULLS LAST, DB_ROLE;

代码说明 #

• FULL OUTER JOIN：按照角色名=数据库名_suffix的规则关联数据，自动保留三类记录：匹配的库与角色对、无对应角色的数据库、无对应库的_suffix角色。
• SELECT子句的CASE逻辑：严格对应你的三个需求，精准控制每列的显示值。
• WHERE过滤：剔除系统数据库和非_suffix结尾的角色，避免无效数据干扰结果。
• 排序规则：用NULLS LAST让空值排在结果末尾，使输出更规整。

执行后将得到符合预期的结果：

DB_NAME     DB_ROLE
mydb        mydb_suffix
newdb       newdb_suffix
test_db     NULL
NULL        test_suffix

内容的提问来源于stack exchange，提问作者Diogo dos Santos