Redshift中IF EXISTS语法报错问题及修复方案咨询

问题原因 #

Redshift的普通SQL是声明式语法，不支持IF这类过程化控制语句（比如IF EXISTS ... THEN），这类语法仅能在PL/pgSQL存储过程或函数中使用，直接在普通SQL语句中写IF会触发语法错误。

修复方案 #

方案1：用CTE实现无IF逻辑的同步（推荐，无需存储过程） #

通过公共表表达式（CTE）先筛选出差异行，再基于CTE完成插入和删除操作——如果CTE无数据，插入/删除会自动跳过，无需显式判断是否存在差异行。

示例代码：

WITH diff_rows AS (
    SELECT t.*
    FROM #temp t
    FULL JOIN dbo.mytable_test mt
        ON t.admincode = mt.admincode
    WHERE 
        -- 处理lastname差异（含一方为NULL的情况）
        (t.lastname <> mt.lastname OR mt.lastname IS NULL OR t.lastname IS NULL)
        -- 处理firstname差异（含一方为NULL的情况）
        OR (t.firstname <> mt.firstname OR mt.firstname IS NULL OR t.firstname IS NULL)
        -- 处理仅存在于mytable_test的行（如果不需要同步这类行可删除）
        OR (t.admincode IS NULL AND mt.admincode IS NOT NULL)
        -- 处理仅存在于#temp的行
        OR (mt.admincode IS NULL AND t.admincode IS NOT NULL)
)
-- 插入差异行到历史表
INSERT INTO dbo.mytable_hist (admincode, lastname, firstname) -- 补充实际需要的字段
SELECT admincode, lastname, firstname
FROM diff_rows
WHERE admincode IS NOT NULL; -- 过滤仅存在于mytable_test的行（按需保留）

-- 从临时表删除差异行
DELETE FROM #temp
WHERE admincode IN (SELECT admincode FROM diff_rows WHERE admincode IS NOT NULL);

方案2：封装到PL/pgSQL存储过程（适合复杂逻辑） #

如果必须保留IF EXISTS的判断逻辑，可将代码封装到Redshift支持的PL/pgSQL存储过程中，过程内可使用过程化控制语句。

示例代码：

-- 创建存储过程
CREATE OR REPLACE PROCEDURE sync_temp_to_hist()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 判断是否存在差异行
    IF EXISTS (
        SELECT 1
        FROM #temp t
        FULL JOIN dbo.mytable_test mt
            ON t.admincode = mt.admincode
        WHERE 
            (t.lastname <> mt.lastname OR mt.lastname IS NULL OR t.lastname IS NULL)
            OR (t.firstname <> mt.firstname OR mt.firstname IS NULL OR t.firstname IS NULL)
            OR (t.admincode IS NULL AND mt.admincode IS NOT NULL)
            OR (mt.admincode IS NULL AND t.admincode IS NOT NULL)
    ) THEN
        -- 插入差异行到历史表
        INSERT INTO dbo.mytable_hist (admincode, lastname, firstname)
        SELECT t.admincode, t.lastname, t.firstname
        FROM #temp t
        FULL JOIN dbo.mytable_test mt
            ON t.admincode = mt.admincode
        WHERE 
            (t.lastname <> mt.lastname OR mt.lastname IS NULL OR t.lastname IS NULL)
            OR (t.firstname <> mt.firstname OR mt.firstname IS NULL OR t.firstname IS NULL)
            OR (mt.admincode IS NULL AND t.admincode IS NOT NULL);

        -- 从临时表删除差异行
        DELETE FROM #temp
        WHERE EXISTS (
            SELECT 1
            FROM dbo.mytable_test mt
            WHERE 
                #temp.admincode = mt.admincode
                AND (#temp.lastname <> mt.lastname OR mt.lastname IS NULL OR #temp.lastname IS NULL)
                AND (#temp.firstname <> mt.firstname OR mt.firstname IS NULL OR #temp.firstname IS NULL)
            UNION ALL
            SELECT 1 WHERE #temp.admincode NOT IN (SELECT admincode FROM dbo.mytable_test)
        );
    END IF;
END;
$$;

-- 调用存储过程执行逻辑
CALL sync_temp_to_hist();

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