PostgreSQL:比较NULL和正常值

Question

我在PostgreSQL 中有一个触发器

CREATE OR REPLACE FUNCTION table_update_func_pk1() RETURNS trigger AS $$
DECLARE
    ri RECORD;
    old_value TEXT;
    new_value TEXT;
BEGIN
    FOR ri IN
        SELECT column_name FROM information_schema.columns
        WHERE
            table_schema = quote_ident('public')
        AND table_name = quote_ident(TG_TABLE_NAME)
        ORDER BY ordinal_position
    LOOP
        EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO new_value USING NEW;
        EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO old_value USING OLD;
        IF new_value <> old_value AND ri.column_name != 'update_by' THEN
            INSERT INTO protokoll(datetime, operation, tabelle, field, pk1, old_value, new_value, update_by)
                   VALUES(now(), TG_OP, TG_TABLE_NAME, ri.column_name, NEW.cfg, old_value, new_value, NEW.update_by);
       END IF;
    END LOOP;
    RETURN NEW;
END;
$$  LANGUAGE plpgsql;

有时，如果old_value从NULL更改为正常值（或从正常值更改为NULL），则条件"new_value<>old_value"不是真的，而是未知的。我想问，有一种方法，我可以得到真实的情况。谢谢

Answer 1

使用is distinct from:

if new_value IS DISTINCT FROM old_value and ri.column_name <> 'update_by' then 
   ...
end if;