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

Writing a script in another language to run the SQL or build the queries might be your best bet. — Henry
– Henry, Commented May 6, 2022 at 5:46
All tables that start with M? This looks like a horrible data model on first glance. (Maybe it isn't and you just have to extend composite keys with a new part, say change a one-company database to a multi-company one which only affects certain tables that can be identified by a part in their name.) — Thorsten Kettner
– Thorsten Kettner, Commented May 6, 2022 at 6:10
"And columns should be added from the front" - you can't new columns can only be appended to the "end" of the table. But the order of columns in a table does not have any meaning anyway, so it doesn't matter if the column is added "at the front" or "at the end". — user330315
– user330315, Commented May 6, 2022 at 8:49

THX1138 · Accepted Answer · 2022-05-07 21:53:53Z

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$$

answered May 7, 2022 at 21:53

THX1138

1,83418 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to merge multiple tables in postgreSQL (using wildcard)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related