如何仅当字段非空时执行PostgreSQL INSERT操作

针对你的需求，这里提供几种实现方式，优先推荐更高效的纯SQL批量插入方案，同时保留原PL/pgSQL循环的修改版本：

方案一：纯SQL批量插入（推荐） #

直接通过INSERT...SELECT语句完成，无需PL/pgSQL循环，处理大量数据时性能更优：

INSERT INTO client_ethnic_identity (client_id, ethnic_identity_id)
SELECT id, ethnic_identity_id
FROM client
WHERE ethnic_identity_id IS NOT NULL;

方案二：在PL/pgSQL循环查询时过滤NULL #

在查询client表时直接筛选出ethnic_identity_id不为空的记录，减少不必要的循环次数：

DO
$$
DECLARE
    rec client;
BEGIN
    FOR rec IN
        SELECT * FROM client WHERE ethnic_identity_id IS NOT NULL
    LOOP
        INSERT INTO client_ethnic_identity (client_id, ethnic_identity_id)
        VALUES (rec.id, rec.ethnic_identity_id);
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

方案三：在PL/pgSQL循环内添加条件判断 #

如果需要保留遍历所有client记录的逻辑，可在循环内增加条件判断，仅当字段有值时执行插入：

DO
$$
DECLARE
    rec client;
BEGIN
    FOR rec IN
        SELECT * FROM client
    LOOP
        IF rec.ethnic_identity_id IS NOT NULL THEN
            INSERT INTO client_ethnic_identity (client_id, ethnic_identity_id)
            VALUES (rec.id, rec.ethnic_identity_id);
        END IF;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

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