Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oj1pV-000468-ER for pgsql-sql@arkaria.postgresql.org; Thu, 13 Oct 2022 17:17:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oj1pS-0003MO-OC for pgsql-sql@arkaria.postgresql.org; Thu, 13 Oct 2022 17:17:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oj1pS-0003ME-EU for pgsql-sql@lists.postgresql.org; Thu, 13 Oct 2022 17:17:06 +0000 Received: from mail.familiegard.de ([2a01:4f8:192:1144::88]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oj1pP-0003Wq-Qw for pgsql-sql@lists.postgresql.org; Thu, 13 Oct 2022 17:17:05 +0000 Received: from [IPV6:2a02:6d40:2143:b901:92f6:52ff:fe00:a664] (unknown [IPv6:2a02:6d40:2143:b901:92f6:52ff:fe00:a664]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits)) (No client certificate requested) (Authenticated sender: frank@familie-gard.de) by mail.familiegard.de (Postfix) with ESMTPSA id E583B2A002F3 for ; Thu, 13 Oct 2022 19:17:00 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=familie-gard.de; s=202201; t=1665681420; bh=G5QrtAjbpVdOot53jJH+/+Uk4f/Qezm3HFVNB3JM/5A=; h=Date:Subject:To:References:From:In-Reply-To:From; b=ILOJcYicqOXB1HjKL50M6e2yCt21V5EHORQwM+eM1NxqFTvGYh49JNKJV5TlxJpzx Yi/Q2cIqY3sqn+/ppxUNXGICuG7YYEIGEHrUlfyCSzf+sQsBqPNJ7K2m8Y6WnuLQgo o6B30uFFyfZmrpcgqxxmWx1eHGqV3gvdNhCgQfCQ= Content-Type: multipart/alternative; boundary="------------iFv68H4InG0oUhs4QQRUUaZP" Message-ID: <06e0486c-341c-a6b8-e076-5e704179714d@familie-gard.de> Date: Thu, 13 Oct 2022 19:17:00 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.3.0 Subject: Re: How to union all tables in a schema into one whole table? Content-Language: de-DE To: pgsql-sql@lists.postgresql.org References: From: Frank Gard In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------iFv68H4InG0oUhs4QQRUUaZP Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. Am 13.10.22 um 19:02 schrieb Shaozhong SHI: > 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 --------------iFv68H4InG0oUhs4QQRUUaZP Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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.

Am 13.10.22 um 19:02 schrieb Shaozhong SHI:
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
--------------iFv68H4InG0oUhs4QQRUUaZP--