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?
3000 tables with the same structure, each having one row? It does seem like it would be better to combine them into a single table, but why did you choose to store you data like that in the first place?– Andriy MMay 15, 2016 at 20:45
Yes, one row. I didn't create the tables. They are all MultiPolygons and represent a small catchment area, and during the calculation process they were stored in separate tables imho.– andscharMay 15, 2016 at 21:00
creating view for them at one time may help.– SpikeMay 16, 2016 at 4:39
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;
Thanks. Your code works nicely except for the INSERT INTO comand. PostgreSQL tells me: <<ERROR: Column "name of my first table" doesn't exist>> Any suggestions? May 18, 2016 at 9:24
1@andrasz fixed the bug (should have been
quote_ident) May 18, 2016 at 9:30
Your code works well for tables with only one row but as it happened to be I just realized that some of the tables contain two rows (the important information also in the first row). How can I add a clause that puts only the first row of each table into consideration? May 18, 2016 at 11:55
@andrasz I added a
LIMIT 1to the query. Keep in mind that there's no first or second in an RDBMS, rows are unordered, so the one retrieved may not be the one you want. Is there anything in the fields you want that make them distinctly your preference? Such as "the one with the lowest value in column X". May 18, 2016 at 12:04
The tables actually have 13 columns. I changed the HAVING operator - which is not the problem. However there are two types of tables derived from different sources (indicated in the name dr% and ws%). The tables have the same number of columns & they are formated equally. Except for the column 'fid', which is in the dr%-tables formated as text and in the ws%-tables formated as PK integer. When I query for one of these "table types" the query works. However querying for all tables results in an ERROR: parse error - invalid geometry HINT: "BW" <-- parse error at position 2 within geometry... May 18, 2016 at 12:59
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)