Subject: How to union all tables in a schema into one whole table?

From: Shaozhong SHI <[email-masked]>
Date: Thu, 13 Oct 2022 18:02:03 +0100

A series of table like tttt.table1, ttt.table2 has been generated in the
schema ttt.

How best to union all these tables into a whole one?

They have the exact same columns.

Regards,

David

---

From: Karsten Hilbert <[email-masked]>
Date: Thu, 13 Oct 2022 19:06:07 +0200

You can use a view, a select with union, or appropriate DDL.

Karsten

Gesendet: Donnerstag, 13. Oktober 2022 um 19:02 Uhr

Von: "Shaozhong SHI" <[email-masked]>

An: "pgsql-sql" <[email-masked]>

Betreff: How to union all tables in a schema into one whole table?

A series of table like tttt.table1, ttt.table2 has been generated in the schema ttt.

Regards,

David

---

From: Frank Gard <[email-masked]>
Date: Thu, 13 Oct 2022 19:17:00 +0200

Hi,

I don't know much about your tables, but maybe (i.e. when they comply with the requirements) you could use table partitioning. Create a partitioned table and use "ALTER TABLE … ATTACH PARTITION …" to bring them all together.

See https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE and the ALTER TABLE statement for details.

Cheers,
Frank.