0

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?

2
  • 1
    Try to remove the “select” keyword from the inner query. Like expects a string expression so the sub query is unwarranted.
    – SQLRaptor
    Oct 11 '19 at 0:40
  • @SQLRaptor, it worked, I also removed the single quoted characters.Thanks!
    – Rogerlr
    Oct 11 '19 at 1:35
1

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.

1
  • That's right, I also tested without parentheses and it worked. Thanks!
    – Rogerlr
    Oct 11 '19 at 1:42

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.