Select tables on column entry in schema (with many tables)

-
I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I query over multiple tables without joining them?

-
Would it be better to merge all these tables into one and execute a simple SELECT query?

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             || ' LIMIT 1';
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;

StackExchange Database Administrators Q#138575, answer score: 4