How to merge multiple tables in postgreSQL (using wildcard)

Question

There are 163 tables, and I want to add two columns in all. And columns should be added from the front. Please tell me how to do it on postgreSQL

This is a table with two columns to add.

col1 | col2
-----+-----
0    | 0
0    | 0

And there are a lot of tables...

[TableA]
colA1 | colA2
------+-----
a     | a
a     | a

[TableB]
colB1 | colB2
------+-----
b     | b
b     | b

.
.
.

And this is what I want finally.

[NewTableA]
col1 | col2 | colA1 | colA2
-----+------+-------+-------
0    | 0    | a     | a
0    | 0    | a     | a

[NewTableB]
col1 | col2 | colB1 | colB2
-----+------+-------+-------
0    | 0    | b     | b
0    | 0    | b     | b

.
.
.

And it should only be applied to tables that begin with 'M'. I don't want to combine 163 times... Please help me

Answer 1

As others have mentioned, there is no mechanism to prepend columns without dropping/recreating tables or individual columns.

To append columns to every table that starts with "m", you can use an anonymous plpgsql code block:

DO
$$
DECLARE tbl_ident regclass;
BEGIN
FOR tbl_ident IN
SELECT
  oid::regclass fqn --this will give you schema qualified, properly quoted table name
FROM
   pg_class
WHERE
  'my_schema'::regnamespace::oid = relnamespace --pick schema(s) you care about
  and relkind = 'r' --ordinary tables only. Does not include views (v),materialized views (m),partitioned tables (p) or foreign tables (f)
  and relname ilike 'm%'
LOOP
  -- append the columns you want to the tables
  EXECUTE 'ALTER TABLE ' || tbl_ident || ' ADD COLUMN col1 integer, ADD COLUMN col2 varchar(1337)';
END LOOP;
END$$