1

I need to match a binary column with its beginning.

For text columns it's easy: LIKE 'image/%'. This uses BTREE index if any.

Is it possible to do this also using an index for a binary column?

BTW, I store UTF-8 encoded strings in Binary, because there is no hard standard that all of them are UTF-8. So, I store a binary representation (maybe even with \0 (zero) bytes), as I cannot use UTF-8.

4
  • I guess your only chance is to convert the binary data to a text value, the apply the LIKE condition on that. Feb 5 at 16:35
  • @a_horse_with_no_name No, this is possible while keeping sargeability and without conversions dbfiddle.uk/… Feb 5 at 21:01
  • I was more thinking of an index on convert_from(... , 'UTF-8') then it could be like this: WHERE convert_from(... , 'UTF-8') like '...' Feb 5 at 21:30
  • 2
    The LIKE operator with bytea arguments works out of the box. That includes using a btree index for prefix matching if available. Just create your index and confirm with EXPLAIN that it's being used. Feb 6 at 0:32

1 Answer 1

0

It appears LIKE just works fine against bytea in PostgreSQL:

create table t(v bytea primary key);
insert into t(v) values
(E'\x12\x31'),
(E'\x12\x34\x56\x78'),
(E'\x12\x35'),
(E'\x12\x34'),
(E'\x12\x38');

Then:

select *
from t
where v like E'\x12\x34%'

gives you:

v
\x12345678
\x1234

db<>fiddle

Check the EXPLAIN plan to see the index is using prefix matching.


You can also use simple half-open interval logic: >= AND <

For example:

select *
from t
order by v;

returns:

v
\x1231
\x1234
\x12345678
\x1235
\x1238

which shows you how the ordering goes.

You can do:

select *
from t
where v >= E'\x12\x34' and v < E'\x12\x35'

which gives

v
\x12345678
\x1234

db<>fiddle

Note that this only works with whole bytes. For half bytes, you need to add a 0 to fill out the byte.


The same thing is possible in SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.