Search code examples
sqlpostgresqlplpgsqldatabase-trigger

PostgreSQL 9.3 operator <> doesn't give logical result


I have this code in a trigger:

CREATE TRIGGER customernametrig
  AFTER UPDATE
  ON customers
  FOR EACH ROW
  EXECUTE PROCEDURE  trig();

and the function:

CREATE OR REPLACE FUNCTION trig()
  RETURNS trigger AS
$BODY$
begin
   if TG_OP='UPDATE' then
    RAISE NOTICE '%', new.customername;
    RAISE NOTICE '%', old.customername;
    RAISE NOTICE '%', new.customername<>old.customername;
    if new.customername<>old.customername then
        RAISE NOTICE 'hi';
    end if;
  end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

the customername column is of type citext.

When I run update command

Update customers set customername='Jack' where customerid=125;  

field it prints:

NOTICE:  Jack
NOTICE:  jack
NOTICE:  f

But if I run select 'jack'<>'Jack' it gives me: t

So What I expect it to print is:

NOTICE:  Jack
NOTICE:  jack
NOTICE:  t
NOTICE:  hi

i don't understand this behavior. What is happening here?


Solution

  • https://www.postgresql.org/docs/current/static/citext.html

    The citext module provides a case-insensitive character string type

    ...

    citext performs comparisons by converting each string to lower case (as though lower were called) and then comparing the results normally. Thus, for example, two strings are considered equal if lower would produce identical results for them.

    t=# select 'jack'<>'Jack';
     ?column? 
    ----------
     t
    (1 row)
    
    t=# select 'jack'::citext<>'Jack';
     ?column? 
    ----------
     f
    (1 row)