PostgreSQL: compare NULL and normal value

Question

I have a trigger in 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;

Sometimes if old_value is changed from NULL to normal value (or from normal value to NULL), the condition "new_value <> old_value" is NOT true but unknown. I would like ask, there is a method that I can get true in the case. Thanks.

user330315 · Accepted Answer · 2016-04-18 14:26:01Z

4

Use is distinct from:

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

answered Apr 18, 2016 at 14:26

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: compare NULL and normal value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related