PostgreSQL LIKE Not Working with CONCAT – Reasons and Solutions

postgresqlpostgresql-9.6

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?

Best Answer

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

To answer your question

COALESCE would be utterly pointless where you placed it to begin with. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the subquery. Related:

Query optimization or missing indexes?

But that's irrelevant, because if a subquery returns no row, then it cannot return any values at all.

To be precise, in your case the subquery may return any number of rows but, when joining to second_table, if there is no row with matching values in (assignment_id, num_60_79) then all columns from the subquery b are filled with NULL values instead. Related:

Multiple correlated subqueries with different conditions to same table

NATURAL FULL JOIN is a very exotic way to join tables. Especially for somebody still learning how to use COALESCE. Quoting the manual:

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

and:

FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).

I think it's safe to assume there is no column named num_60_79 in second_table and you surely wouldn't want to involve it in the join conditions if there was one. You only want to join on the column assignment_id, and that's what you should put in the query instead of the NATURAL key word.

Also, while FULL JOIN is theoretically possible here, we would typically see a LEFT JOIN.

You also don't need two layers of subqueries and some other noise in the query.

Proper query

All things considered, while you are ...

trying to count the number of columns that is retrieved from the subquery

... my educated guess is you want this query instead:

SELECT s.*, COALESCE(b.num_60_79, 0) AS num_60_79
FROM   second_table s
LEFT   JOIN (
   SELECT assignment_id, count(*) AS num_60_79 
   FROM   avg_required_table
   WHERE  mark_as_percent >= 60 
   AND    mark_as_percent <  80
   GROUP  BY assignment_id
   ) b USING (assignment_id);

Best Answer

Related Solutions

PostgreSQL – Why SET LOCAL statement_timeout Doesn’t Work as Expected with Functions

PostgreSQL – Why COALESCE Function Not Working in Query?

To answer your question

Proper query

Related Question