5
  • 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 M May 15 '16 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. – andschar May 15 '16 at 21:00
  • creating view for them at one time may help. – Spike May 16 '16 at 4:39
4

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? – andschar May 18 '16 at 9:24
  • 1
    @andrasz fixed the bug (should have been quote_literal instead of quote_ident) – Ziggy Crueltyfree Zeitgeister May 18 '16 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? – andschar May 18 '16 at 11:55
  • @andrasz I added a LIMIT 1 to 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". – Ziggy Crueltyfree Zeitgeister May 18 '16 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... – andschar May 18 '16 at 12:59
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.