Why does LIKE not work with CONCAT but works with STRING value
Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').


The first is an untyped string literal. The second is type text since the function concat() returns text. Not exactly the same thing for Operator Type Resolution! Quoting the manual there:


  
  Look for the best match.
  
  a. Discard candidate operators for which the input types do not match
  and cannot be converted (using an implicit conversion) to match.
  unknown literals are assumed to be convertible to anything for this
  purpose. If only one candidate remains, use it; else continue to the
  next step.
  


And there is no implicit conversion for text -> bytea in standard Postgres.
And there is no variant of LIKE for bytea / text, only for:

bytea / bytea
name / text
text / text

db&lt;>fiddle here (testing in Postgres 11)

To solve, add an explicit cast to bytea (text can be cast to any type):

SELECT CONVERT_FROM(data, 'utf8')
FROM   binary_data
WHERE  "data" LIKE ('%' || 'me' || '%')::bytea  --!
LIMIT  10;