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 1kPhjf-00085i-Bd for pgsql-admin@arkaria.postgresql.org; Tue, 06 Oct 2020 07:50:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kPhje-00011F-4m for pgsql-admin@arkaria.postgresql.org; Tue, 06 Oct 2020 07:50:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPhjd-00010y-Nf for pgsql-admin@lists.postgresql.org; Tue, 06 Oct 2020 07:50:09 +0000 Received: from sonic309-22.consmr.mail.ne1.yahoo.com ([66.163.184.148]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kPhja-0004Q5-32 for pgsql-admin@postgresql.org; Tue, 06 Oct 2020 07:50:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1601970604; bh=Rxx6ymZSrRy387y9Ni5anhGKxRWCXaJx0ElvqMbgQbU=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=EXv6ZlTbZmrknZxL42T87HwZxogDMQU2LRV0nqXIQlHVjQlyBKj6v/8VFzeiTgck18a098hRCdRDDzbDB2lk8jMIUJCUF3FkAnA9xyUkMBmfVpDiSFyrhq1Af1lQgFya1xDaWj+26W1HnQkbZjoiK1S/A+tRhQ4v1ju2Ol6F85R78r6vhDd3ms4Vir05T5D6dEJIfxTAeAahi6zlP4kmAstpuFT6k3Wv9tkHyxbyLw3woXVO7dLlJu0Kc79xDDVK6Dvti8F1P8AdiiJnYhi1loJPx/DA5IQV/S+yrlzixOWy1/OkzEusC/PnTrZF179wJPizl9CmUpNEWV6qqL5qlQ== X-YMail-OSG: 1rTB89IVM1mjhMnqIzbkhPkBRO6szmGyl2hj2g48pGv1Zlr.giHPZAtbopyADae mNKv89hqDPNmlsQMrlPs18moGLFxTwifktZsyJtzwnv0N4iXgZJirsOMcwdvMEfx7gnlku_t9kCa nnjh7YfCJa7rLTwtq8X6dF1QJO0PXuZf6QGABVkGAquADQtDEsXOSxOoBOYJH3oXvkxk3t_h41yv UVKM.9vu6fOjja773dNq5rxTREdGvh1HgeBY5C81x7zOJEa8mRlVjdN7VYhpVbbYC9tGI6LadMeV ..iu7pr5mtf0KehJKAKFwSUMTl9RpFVK_VuCA5bOMVqKNKYCqiN5OHxJ7jkIDDknvyEJQyROcxu6 rojGUHgvz91IlNIc2wElcz8WY0vKnp5TN1wNLJdjFjJjnPZeoqTx9ZLI1SUNLKk8o9thGPnvjl5n MRttLsEIKlyx7V92SVJmto2KHw9j6x60GOgKFJG7PJ6f4GUCU4arEgAOcMBG3Rl20xTXIqjJp7yk OW.f_zvQnKzEczQTIakDm.NpLsBR6Q6bljOF_IhFp_liDeapUFA4a3geU9Gq2DWGhWtvJx990N80 ZJcjfm_WjEDSTaRH5s5rLMc0x1wpZwKqbqi1lMpIGp7w4T30xJ_gWhtwhABX8adxyxjZoihqRNge pgzv6KOoxUdOGLFMAB622Mn3529h6QesFh4qzZtx_pM9wHgOU_3fTNdgDz4HQFZDOKaATrVLf.uf 1mtz2mY2fagbu_WxHz7VcojGFdugtnA_gyZ2uteKoMxgvrLTsYkUr7ry4nNS2Ml_n_EE2lKrXCZf 5ZCQ4vDgulmzpJ9o6m4bNpb7IpvwpuM2SdoBtSMQDFJI_fWw..x2SFIcTvSIaqkK5KnYp.HJy8Xl M3QngGHXJ5e_fDzroyzCFi.zbiD2AjXt9TN4ya442XcYvbgkHC3yytnuOaty72Stg12rSfAy7YDf 15WOjaD6PkkePP8po8_.5hD_5ictgWo5veFRYik34gH1aqMv584BetHQ_HwsVnh_Gw6Ov6s22jHD PLqgKa5KMGNj50fhf5PLo_G7m3fft3K1bObYpIMVgCUXZa1ul2zxrPRVKrzPdXW7vyiJ4TVb5lF9 vhlOeyTXQVbmoY8O8M_jXxmZ71qehiEP3a2LZrj70NAEjwKl.GLjocEXF_hPCv4T5ZGAstT41SLx Ybqgj1z1NupRRssME3RINxV7_dY2lzCVqRygU6.nWOn4vC8fOJIrlaH4Y4ejOoaWC2KOJtaFGxfn duJMLIgYqaIuSdPwX0EAi25X8Yk00Hy6qOVZ6SJQNeKL8wkXxYw4x7EJJ92fO0QVVmxfhlt.0Ys2 H3W_Z2V2OTeHjzASVkBOXT102ihlsUHmpNUUqp.WDwpvk1RFjLR4DSKdPCrieJMOZzhZK70POEVH wuO8VaAZnonms1Ilyr7y6RYi5KBNQbTHe9tYV_O4DMHmIjgwr_dzA2GbaMVlTyzahfxPf2UumKZu BW52RtwC0Kxdd8Ux2DRQm7URHwmpr6xuRvy4fo9rEvn3rjA-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.ne1.yahoo.com with HTTP; Tue, 6 Oct 2020 07:50:04 +0000 Date: Tue, 6 Oct 2020 07:50:02 +0000 (UTC) From: Nagaraj Raj To: Naveen Kumar Cc: pgsql-admin Message-ID: <244012893.2409051.1601970602436@mail.yahoo.com> In-Reply-To: References: Subject: Re: How to add partitions to the existing table in PostgreSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2409050_189538655.1601970602435" X-Mailer: WebService/1.1.16718 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36 Content-Length: 3079 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2409050_189538655.1601970602435 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =20 There is no way to add a partition to an existing table. All you need to do= create a new table with the partition on the desired column and copy data = from old to new then do alter table name.=C2=A0 Thanks,Rj On Monday, October 5, 2020, 11:31:28 PM PDT, Naveen Kumar wrote: =20 =20 How to add partitions to the existing table in PostgreSQL for ex, below is my table definition: CREATE TABLE ot.employee ( =C2=A0 =C2=A0 empno smallint NOT NULL, =C2=A0 =C2=A0 ename character varying(20), =C2=A0 =C2=A0 job character varying(20), =C2=A0 =C2=A0 deptno smallint ) Now I would like to add partition to deptno column I don't see any alter table command to add the partition to the existing ta= ble. Please help me on this. Thank youNaveen =20 ------=_Part_2409050_189538655.1601970602435 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

There is no way to add a partition to an existing table. All you need to do create a new table with the partition on the desired column and copy data from old to new then do alter table name. 



Thanks,
Rj





On Monday, October 5, 2020, 11:31:28 PM PDT, Naveen Kumar <naveenmcp@gmail.com> wrote:


How to add partitions to the existing table in PostgreSQL

for ex, below is my table definition:

CREATE TABLE ot.employee
(
    empno smallint NOT NULL,
    ename character varying(20),
    job character varying(20),
    deptno smallint
)

Now I would like to add partition to deptno column

I don't see any alter table command to add the partition to the existing table.

Please help me on this.

Thank you
Naveen

------=_Part_2409050_189538655.1601970602435--