PostgreSQL query with subquery doesn't working as expected
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.