LIKE or match by beginning for binary strings in PostgreSQL

Question

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.

Answer 1

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.