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;
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)