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 1kdGcC-00035K-H4 for pgsql-admin@arkaria.postgresql.org; Thu, 12 Nov 2020 17:42:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdGcB-0001LM-B1 for pgsql-admin@arkaria.postgresql.org; Thu, 12 Nov 2020 17:42:31 +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 1kdGcB-0001FS-0k for pgsql-admin@lists.postgresql.org; Thu, 12 Nov 2020 17:42:31 +0000 Received: from mail-qk1-x72c.google.com ([2607:f8b0:4864:20::72c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kdGc8-0003Dq-2f for pgsql-admin@postgresql.org; Thu, 12 Nov 2020 17:42:30 +0000 Received: by mail-qk1-x72c.google.com with SMTP id d9so6052780qke.8 for ; Thu, 12 Nov 2020 09:42:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=RKHYlQ2uXJqsuIMSxJmMhd6PgRuKamcPspm9FFAZIBU=; b=VCFztjLXgW7Ct51fbSkgGSVtYw6kx73zQiabYJowyp29Ka119KHVy/uW3VS6KKDINp 7XBFi6xULnZ2M+4oyDNyDxUoc1HiKfE2NoEtSJ0uwX9U7VZDeGzHQ3uIzoLqIMs9nz1V o26Wb3l8QBH1c4mRvuu0fowtQKnTd7uk8K/lsrmFBcUiVYqsYc7CRfbdPwSK9DKQZEf5 wcZ6R97wq3zYllAVwdKlu/PKaeHyrK64iwYlTEV4w2eBCPYkEoTRniigH55G5s4Xd3YB j1x0/BSK2Fhz8FZaVEQI/pwzCOsjL3BiM/Gxf8u9kb3LTyh9DswdP+ezn+F1jxVGrwFk Lamw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=RKHYlQ2uXJqsuIMSxJmMhd6PgRuKamcPspm9FFAZIBU=; b=GesAaTkY2KrthNCesPjWeeyd/PJwQqVAuiGBtTNoq6qIO8ZQcp67fsqndgiitOrukB 9teCl5jNdDiB7xLHFPjj/TwEd3MxeN7UHJRRYm9wPpPaBxpFO8RYwLjL8Tmmd5hYIV2l /uk7ebq1eiBWRa8mINku6ErPMCWfgxeQJoLlQrqs+6doQKPzx4kr3aIzC48rcaVhyPBh tmvT+vlaQhclw7IUis3dTDqwdXE4RGmldoXMard59GjqvatiFQCMO7kv8MsMRFWtLWlX symsdzQyv6atQwiytd0bTgEEqVP3TmBFq3sx1dblrUmios4Us7lDiyOjrB6rvIawRC0V XKrw== X-Gm-Message-State: AOAM531M5WjQb4ijUXP6IR1CyZS0Q3HdyDFr1cFWdQ3/gdnvbWPLWh72 gMznY4+z47wg4/UQXjOdNgw= X-Google-Smtp-Source: ABdhPJz+hwJHXmifxL6smicG/SVctNPLkzJqX2K0AA7flyBoNltUPaO14RihYl/RGfRb4YHue/goiA== X-Received: by 2002:a05:620a:218b:: with SMTP id g11mr956345qka.190.1605202946364; Thu, 12 Nov 2020 09:42:26 -0800 (PST) Received: from [192.168.254.66] (h217.104.141.67.dynamic.ip.windstream.net. [67.141.104.217]) by smtp.gmail.com with ESMTPSA id h65sm5191782qkd.95.2020.11.12.09.42.25 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 12 Nov 2020 09:42:25 -0800 (PST) Content-Type: multipart/alternative; boundary=Apple-Mail-8CB772F6-0DEF-48F2-ADB9-D1C8BF0D1683 Content-Transfer-Encoding: 7bit From: John Scalia Mime-Version: 1.0 (1.0) Subject: Re: Working with partition tables Date: Thu, 12 Nov 2020 12:42:23 -0500 Message-Id: <910F94F6-4FE6-4A22-8E1E-B790A4F7FEB0@gmail.com> References: Cc: pgsql-admin In-Reply-To: To: "David G. Johnston" X-Mailer: iPad Mail (18A8395) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail-8CB772F6-0DEF-48F2-ADB9-D1C8BF0D1683 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Thanks David, I had only looked at the docs for v.11, as that is what we=E2=80= =99re running, and I didn=E2=80=99t see this line.=20 We decided to use a Create table temp (LIKE original); and make the alterati= ons there, then create a third table from like the temp but with partitionin= g, did the same for each partition, then linked those as partitions to the n= ew temp with the partitioning enabled. Finally, we just renamed that third t= emp to the original name. Was just hoping for a less circuitous route. Sent from my iPad > On Nov 12, 2020, at 11:25 AM, David G. Johnston wrote: >=20 > =EF=BB=BF >> On Thu, Nov 12, 2020 at 9:18 AM John Scalia wrot= e: >> Is there any way to remove the partition key, or do we simply need to cre= ate a new table with the structure we need, drop the old one, then rename th= e new one to that original name and reattach the partitions? > =20 > I'm not super familiar with partitioning but...you can probably do it in t= he reverse order and avoid the rename. A partitioned table without partitio= ns seems useless so just drop it, then create a new one with the same name w= ith the correct defintion. >=20 > The documentation is indeed clear though you may be looking in the wrong s= pot: > https://www.postgresql.org/docs/13/ddl-partitioning.html > "It is not possible to turn a regular table into a partitioned table or vi= ce versa." >=20 > David J. >=20 --Apple-Mail-8CB772F6-0DEF-48F2-ADB9-D1C8BF0D1683 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Thanks David, I had only looked at the docs= for v.11, as that is what we=E2=80=99re running, and I didn=E2=80=99t see t= his line. 

We decided to use a Create table temp (LI= KE original); and make the alterations there, then create a third table from= like the temp but with partitioning, did the same for each partition, then l= inked those as partitions to the new temp with the partitioning enabled. Fin= ally, we just renamed that third temp to the original name. Was just hoping f= or a less circuitous route.

Sent from my iPad
<= div dir=3D"ltr">
On Nov 12, 2020, at 11:25 AM, D= avid G. Johnston <david.g.johnston@gmail.com> wrote:

=EF=BB=BF
= On Thu, Nov 12, 2020 at 9:18 AM John Scalia <jayknowsunix@gmail.com> wrote:
Is= there any way to remove the partition key, or do we simply need to create a= new table with the structure we need, drop the old one, then rename the new= one to that original name and reattach the partitions?
 
I'm not super familiar with partitioning but...you can probab= ly do it in the reverse order and avoid the rename.  A partitioned tabl= e without partitions seems useless so just drop it, then create a new one wi= th the same name with the correct defintion.

The document= ation is indeed clear though you may be looking in the wrong spot:
https://w= ww.postgresql.org/docs/13/ddl-partitioning.html
"It is not po= ssible to turn a regular table into a partitioned table or vice versa."

David J.

= --Apple-Mail-8CB772F6-0DEF-48F2-ADB9-D1C8BF0D1683--