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 1mdVGU-0001hi-Jw for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Oct 2021 10:25:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mdVGT-0005ZQ-FZ for pgsql-bugs@arkaria.postgresql.org; Thu, 21 Oct 2021 10:25:37 +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 1mdVGT-0005Z9-7u for pgsql-bugs@lists.postgresql.org; Thu, 21 Oct 2021 10:25:37 +0000 Received: from relay7-d.mail.gandi.net ([217.70.183.200]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mdVGQ-0002xk-TR for pgsql-bugs@lists.postgresql.org; Thu, 21 Oct 2021 10:25:36 +0000 Received: (Authenticated sender: vik@postgresfriends.org) by relay7-d.mail.gandi.net (Postfix) with ESMTPSA id 948B120004; Thu, 21 Oct 2021 10:25:32 +0000 (UTC) Subject: Re: Null option and Default value Lost when use CREATE TABLE AS to backup a table. To: "Xiao, Bing (Benny)" , "pgsql-bugs@lists.postgresql.org" References: From: Vik Fearing Message-ID: Date: Thu, 21 Oct 2021 12:25:32 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.13.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=windows-1252 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/21/21 7:47 AM, Xiao, Bing (Benny) wrote: > > 1. Create back up table. > > create table contact_bckp as table contacts with data; This is not the correct way to do this. Do it this way instead: BEGIN; CREATE TABLE contact_bckp (LIKE contacts INCLUDING ALL); INSERT INTO contact_bckp TABLE contacts; COMMIT; -- Vik Fearing