1

Please look on the following scenario;

my_db=# select version();
                                                              version
------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Debian 9.6.9-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE binary_data ("data" BYTEA NOT NULL);
CREATE OR REPLACE FUNCTION random_string(lengh integer) 
RETURNS varchar AS $$ 
  SELECT array_to_string(ARRAY(
          SELECT substr('abcdefghijklmnopqrstuv',trunc(random()*21+1)::int,1)       
             FROM generate_series(1,$1)),'')
$$ LANGUAGE sql VOLATILE;
insert into binary_data ("data") select CONVERT_TO(random_string(64), 'utf8') FROM generate_series(1,1000000);

Now if we run following query it works really well;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like '%me%' limit 10;

Bu if we run following query;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like CONCAT('%', 'me', '%') limit 10;

It failed with following error

Kernel error: ERROR:  operator does not exist: bytea ~~ text

Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').

First guess was the type must be different to check it I run:

SELECT pg_typeof('%me'), pg_typeof(CONCAT('%', 'me', '%'));

And yes my guess was right;

unknown text

So '%me%' is treated as unknown and CONCAT('%', 'me', '%') is treated as text. To use the same query with CONCAT('%', 'me', '%')::UNKNOWN result to another error.

Kernel error: ERROR:  failed to find conversion function from unknown to bytea

Any one have any idea what's going wrong with above queries?

  • Have you tried to change RETURNS varchar AS $$ by RETURNS text AS $$? It seems just a CAST issue. – McNets Nov 5 '18 at 11:45
  • Please always start by declaring the version of Postgres you use. SELECT version(); helps – Erwin Brandstetter Nov 5 '18 at 11:51
  • @ErwinBrandstetter Just updated and added the version. – Nazar Hussain Nov 5 '18 at 13:46
4

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:

  1. 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<>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;

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.