Return to Revisions

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
    _tbl TEXT;
    
  BEGIN
    FOR _tbl IN (
      SELECT
        pt.table_name
      FROM
        pg_tables AS pt
      WHERE
        pt.schema_name = '<source_schema>'
        AND
        pt.table_name LIKE '<source_tables_common_name_pattern>'
    ) LOOP
      RAISE NOTICE 'Merging table: %...', _tbl;

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

          -- DROP TABLE %1$I CASCADE;
        $FORMAT$,
        _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