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?
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)