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

Question

• 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?

Answer 1

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;

Answer 2

I assume you're running PostGIS to handle your spatial data. If so, you have a view (PostGIS 2.0 or later) or a table (PostGIS 1.5 or earlier) called geometry_columns, and it might save you some work in this case. You can use something like...

SELECT f_table_name FROM public.geometry_columns --public schema by default
WHERE f_table_schema = 'target_schema' AND f_geometry_column = 'geom';

...to generate a distinct list of all the tables in your target schema with a spatial column called geom. You should be able to plug that into the dynamic command provided by @Ziggy Crueltyfree Zeitgeister, replacing the query between FOR _r IN...and...LOOP.

(might be more of a comment than an answer but i lack the rep)