I have a system where I need to compare bytea values (case insensitive in this case). For this purpose, I use encode to convert the bytea values into strings which I then compare. This works as long as there is no unicode character in the array.
select
encode(cid, 'hex'),
cid = 'x3030386132323035'::bytea a,
encode(cid, 'escape') = encode('x3030386132323035'::bytea, 'escape') b,
encode(cid, 'escape') like encode('x3030386132323035'::bytea, 'escape') c,
encode(cid, 'escape') ilike encode('x3030386132323035'::bytea, 'escape') d
from history
| encode | a | b | c | d |
|---|---|---|---|---|
| 3030386132323035 | true | true | true | true |
When there are unicode characters in bytea, however, like/ilike do not work
select
encode(cid, 'hex'),
cid = 'x303030d0a2d095d0a1d0a231'::bytea a,
encode(cid, 'escape') = encode('x303030d0a2d095d0a1d0a231'::bytea, 'escape') b,
encode(cid, 'escape') like encode('x303030d0a2d095d0a1d0a231'::bytea, 'escape') c,
encode(cid, 'escape') ilike encode('x303030d0a2d095d0a1d0a231'::bytea, 'escape') d
from history
| encode | a | b | c | d |
|---|---|---|---|---|
| 303030d0a2d095d0a1d0a231 | true | true | false | false |
Can anyone explain what is causing this behavior?