SQL solution using a simple `DO` block.

This assumes you have a `<target_schema>.<target_table>` in place to receive the `INSERT`ed 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:

```SQL
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
<br><br>
- 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