PostgreSQL – Subquery Not Working as Expected

postgresql

I'm trying to make a query that should return records based on a subquery. Here's an example:

select … where tablename like ('%subquery_result%');

The subquery is generating the string as expected:

appldb=# select '''%_p' || 
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''';
   ?column?
---------------
 '%_p2019_09%'
(1 row)

Using the string above, records are returned:

select tablename from pg_tables 
where tablename like '%_p2019_09%' limit 2;
      tablename
---------------------
 part_p2019_09_26
 part_p2019_09_29
(2 rows)

But when I use the complete query I have no return:

appldb=# select tablename 
from pg_tables 
where tablename like ( select '''%_p' || 
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''' );
 tablename
-----------
(0 rows)

I have already tried to remove parentheses around the subquery. But the query got in error.

Did I miss any steps to ensure where clause to interpret the subquery?

Best Answer

You are including literal single quote marks into the LIKE query (as shown in the output of your first query). These literal quote marks are not present in the data, so they do not match.

select tablename 
from pg_tables 
where tablename like ( select '%_p' || 
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

including the select is harmless in this case, but it is not necessary:

select tablename 
from pg_tables 
where tablename like ( '%_p' || 
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

You don't even need the parentheses around the whole thing, as || binds tighter than LIKE, but I didn't know that until I did the experiment.

Related Question