You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery). But you can build and execute a dynamic SQL statement for this. For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name 
           FROM information_schema.tables 
          WHERE table_schema = 'your_desired_schema'
LOOP
    sql_string := sql_string || format($$
        -- some whitespace is mandatory here
        UNION
        SELECT name FROM %I
    $$,
    i.table_name);
END LOOP;

EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable. You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables. If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

• the format() function was introduced in version 9.1
• I've omitted some mandatory elements of any PL/pgSQL block to keep it simple
• the earlier version mentioned using a DO block. The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.

You can use a derived table that contains the counts per post_category_id and left join it to the post_categories table

select p.*, coalesce(t1.p_count,0)
from post_categories p
left join (
    select post_category_id, count(*) p_count
    from posts
    group by post_category_id
) t1 on t1.post_category_id = p.id

You could create a function which will be accessing all your tables:

CREATE OR REPLACE FUNCTION some_schema.some_function_name()
returns TABLE(created_by varchar(300), created_at varchar(300))
AS $BODY$
DECLARE
  stmt text;
  BEGIN
    stmt = (
        WITH relevant_tables AS (
           SELECT table_name,
                  CONCAT('SELECT created_by::varchar(300), created_at::varchar(300) FROM ', table_name) as table_query
           FROM information_schema.tables 
           WHERE <some criteria to limit tables being searched>
        )
        SELECT string_agg(table_query, ' UNION ALL ') AS final_query
        FROM relevant_tables a
    );
    return query EXECUTE stmt;
  end; $BODY$
language plpgsql
;

I do not know what are your data types so I casted everything to varchar(300).

Having this function, you can then call:

SELECT * FROM some_schema.some_function_name()

You can also add any WHERE you like.