Merge multiple tables (100 tables) into a new table in PostGIS

Question

I am looking to merge a large number of individual tables into a new table in PostGIS. I have almost 100 "identical" tables so I wanted to do it automatically. There is a good answer here Merge Multiple Tables into a New Table in PostGIS but it is just for a few tables.

Answer 1

SQL solution using a simple DO block.

This assumes you have a <target_schema>.<target_table> in place to receive the INSERTed data; I would prefer this over e.g. a collective CREATE TABLE statement (which is faster) to explicitly define column types (and typemods) and keys/constraints:

DO
$DO$
  DECLARE
    _shm TEXT;
    _tbl TEXT;
    
  BEGIN
    FOR _shm, _tbl IN (
      SELECT
        pt.schemaname,
        pt.tablename
      FROM
        pg_tables AS pt
      WHERE
        pt.schemaname = '<source_schema>'
        AND
        pt.tablename LIKE '<source_tables_common_name_pattern>'
    ) LOOP
      RAISE NOTICE 'Merging table: %.%...', _shm, _tbl;

      EXECUTE FORMAT(
        $FORMAT$
          INSERT INTO <target_schema>.<target_table>(<optional_column_list>) (
            SELECT * FROM %1$I.%2$I
          );

          -- DROP TABLE %1$I.%2$I CASCADE;
        $FORMAT$,
        _shm, _tbl
      );
    END LOOP;
  END;
$DO$
;

Notes:

• run the outer LOOP query individually prior to executing the DO block to verfiy the correct tables are selected
• <source_tables_common_name_pattern> needs to be used in conjunction with the % wildcard to make sense
• <optional_column_list> should be used e.g. if you have an auto-incremental PRIMARY KEY identifier in the <target_schema>.<target_table> - you may want to also specify the exact columns in the SELECT list inside the INSERT statement, rather than using *
• run VACUUM ANALYZE <target_schema>.<target_table> when done
  
  
• OPTIONALLY: uncomment the DROP TABLE statement if you want to also delete the current source table in one go - otherwise you can run this same DO block with only the DROP TABLE statement afterwards