LIKE or match by beginning for binary strings in PostgreSQL



  • 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.



  • 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

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ae53c517dc56ba727f95c0ddeb6967f9

    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

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6923b8136e2c76612613276d8e20ad00

    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 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b0152ab1005578c968f091b8b17de81f .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2