Python传入None检测PostgreSQL含Null行失败，如何解决？

问题根源 #

Python的None确实对应PostgreSQL的Null，但SQL的逻辑规则和Python不同：在SQL中，Null不等于任何值，包括它自己。你原来的函数里用TEGENREKENING=_tegenrekening，当传入的_tegenrekening是Null时，这个条件会返回Null（被视为False），导致EXISTS子句判定无匹配行，最终返回False。

方案1：修改PostgreSQL函数 #

直接调整函数里的条件判断，处理Null的匹配场景，有两种写法：

写法1：显式判断Null

CREATE OR REPLACE FUNCTION public.control2(_datum date, 
_tegenrekening character varying)
 RETURNS TABLE(bestaat boolean)
 LANGUAGE sql
AS $function$
BEGIN
    RETURN QUERY
    SELECT EXISTS (
        SELECT 1 FROM "rekening" 
        WHERE "DATUM" = _datum
        AND ("TEGENREKENING" = _tegenrekening OR ("TEGENREKENING" IS NULL AND _tegenrekening IS NULL))
    ) as bestaat
    LIMIT 1;
END
$function$

写法2：使用IS NOT DISTINCT FROM（更简洁）

PostgreSQL提供的IS NOT DISTINCT FROM操作符会将Null视为相等，直接替代=即可：

CREATE OR REPLACE FUNCTION public.control2(_datum date, 
_tegenrekening character varying)
 RETURNS TABLE(bestaat boolean)
 LANGUAGE sql
AS $function$
BEGIN
    RETURN QUERY
    SELECT EXISTS (
        SELECT 1 FROM "rekening" 
        WHERE "DATUM" = _datum
        AND "TEGENREKENING" IS NOT DISTINCT FROM _tegenrekening
    ) as bestaat
    LIMIT 1;
END
$function$

方案2：在Python代码中动态处理条件 #

如果不想修改数据库函数，可以在Python端根据参数是否为None，生成对应的查询语句（以psycopg2为例）：

import psycopg2

def check_row_exists(datum, tegenrekening):
    # 替换为你的数据库连接参数
    conn = psycopg2.connect(dbname="your_db", user="your_user", password="your_pwd", host="your_host")
    cur = conn.cursor()

    if tegenrekening is None:
        query = """
            SELECT EXISTS (
                SELECT 1 FROM "rekening" 
                WHERE "DATUM" = %s AND "TEGENREKENING" IS NULL
            )
        """
        cur.execute(query, (datum,))
    else:
        query = """
            SELECT EXISTS (
                SELECT 1 FROM "rekening" 
                WHERE "DATUM" = %s AND "TEGENREKENING" = %s
            )
        """
        cur.execute(query, (datum, tegenrekening))

    exists = cur.fetchone()[0]
    cur.close()
    conn.close()
    return exists

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