By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--like / ilike with encode(bytea) not work with unicode
--https://stackoverflow.com/q/78632987/5298879
/*
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.
*/
create table history(cid)as values('\x3030386132323035'::bytea)
,('\x303030d0a2d095d0a1d0a231'::bytea);
SELECT 2
select cid as_default,
encode(cid, 'hex') as_hex,
encode(cid, 'escape') as_escape,
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,
encode(cid, 'escape') like encode('\x3030386132323035'::bytea, 'escape') escape '!' e,
encode(cid, 'escape') ilike encode('\x3030386132323035'::bytea, 'escape') escape '!' f
from history
where cid = '\x3030386132323035'::bytea;
/*
encode a b c d
3030386132323035 true true true true
*/
| as_default | as_hex | as_escape | a | b | c | d | e | f |
|---|---|---|---|---|---|---|---|---|
| \x3030386132323035 | 3030386132323035 | 008a2205 | t | t | t | t | t | t |
SELECT 1
--When there are unicode characters in bytea, however, like/ilike do not work
select cid as as_default
, encode(cid, 'escape') as as_escape
, cid = '\x303030d0a2d095d0a1d0a231'::bytea as a
, encode(cid,'escape')
= encode('\x303030d0a2d095d0a1d0a231'::bytea,'escape')
as b
, encode(cid,'escape')
like encode('\x303030d0a2d095d0a1d0a231'::bytea,'escape')
as c
, encode(cid,'escape')
ilike encode('\x303030d0a2d095d0a1d0a231'::bytea,'escape')
as d
, encode(cid,'escape')
like encode('\x303030d0a2d095d0a1d0a231'::bytea,'escape')
escape '' --escaping disabled
as e
, encode(cid,'escape')
ilike encode('\x303030d0a2d095d0a1d0a231'::bytea,'escape')
escape '!' --escape character changed from default `\` to `!`
as f
from history
where cid='\x303030d0a2d095d0a1d0a231'::bytea;
--encode a b c d
--303030d0a2d095d0a1d0a231 true true false false
--Can anyone explain what is causing this behavior?
| as_default | as_escape | a | b | c | d | e | f |
|---|---|---|---|---|---|---|---|
| \x303030d0a2d095d0a1d0a231 | 000\320\242\320\225\320\241\320\2421 | t | t | f | f | t | t |
SELECT 1
select '\a' like '\a';
select '\a' like '\a' escape '';
select '\a' like '\a' escape '!';
| ?column? |
|---|
| f |
SELECT 1
| ?column? |
|---|
| t |
SELECT 1
| ?column? |
|---|
| t |
SELECT 1