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 1kNNNl-0001NN-PI for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 21:41:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNNNk-0000Hh-DJ for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 21:41:56 +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 1kNNNk-0000Fu-5q for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 21:41:56 +0000 Received: from sonic309-21.consmr.mail.ne1.yahoo.com ([66.163.184.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kNNNg-0006gB-Qe for pgsql-bugs@postgresql.org; Tue, 29 Sep 2020 21:41:55 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1601415709; bh=jRRgc1ab22KEtJAhD486aVDqyxeH28MQxXU5imlNn/U=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject; b=GaETO9HabZSecjlinWbKoZI0As7DYP1qx0qmHDUgOi1SoV9a+6wy3Y7cI4TufIFmEzVLHxHD10WeSP0Q3ZSBccDTVRCbsTqyyFGSwTsyc30AveFBqeOJ5nMTzimX55qOM4mWyMj+7+5FJKQSuY4Yf579q/namE4xASaxbtOGMYU9ZnXMTD1VWTZpitcWy3RroBWClb2nUClbcnNwVsarAKCWaOP83rDtvd7pBj81vnyQ/oAcVzvcamy3vIq2DFDzOtTEq8DyMrLslJ9UQg2XsVfdWGOhojiITBRJDlWr0y3d8eNe5rrwAcq55BdjI8KWT9ihQ7k+MdPngyZd532Rvw== X-YMail-OSG: idft_t8VM1lIHyVJ9HqR9u68sDkzPxPyCMIHcWBlKIb2DXJhleh83WL6KykFwuY 2Nm3Q0cOcFdWBWimkxtdmjyBZZsPUhDs6ERydn_S1sVX0Io.THXBtRtofyvwGgXsj8mn5b6gMDgS Gb4_WDCJgz9560FoaTBiCB2IX9nvn6Abpg1QlQVM7eW3WGatTj.Zp1A7K2OoUfWz_pcoHjDai.PD OY5ekU5QU7y9itNSl.mQgDGPLYZ8wT9qDdvlEjAnO95GOmX3ToukMdJhe2xKReKAT.xTXImZbo7K v_nf3lLimwb_EU1c1aaB1alezXzFLeWeR80W2YIe2xLPd.W.M9ECKebosas.VH9RgR7Qd.7lXC8X iPVQL5gUjjiwZ8ca7iuB3OSUS1sjid3VH.ya_uSfYhkubZH07R53uFomyQl.1fbQj1N_iHKzVw9I 9wDHNrkrAep9JJ8Cr7BTkhLIwl2fgyfDEA296KnfSNigNEiRbuemziH0JQwLffSYElp71f7qcPYC M6NSSf9OWrmwUseFhgHxHAwgTV7b0rbXGdT3zGpDNcrrCnfsW_Wfd4o3zGtmPMn4irgrhXqUKX_e mmK4CFDRojt99ZiA5Q7IOvT0mAkicdhaPWnq_Mcue2XgqVZ8WYhSZGB74VmwqKZQtkQ.6_wqTNTS J9ncuhmyfh.CaEAke4Qn5TwAx7hsFxyZi1TShInHHw8uzc0R18x1dmmmRn_qhevHvFRuxxSF.iPl hEDgJ6hndiJ3.i1w35QFBajv9nm3lV4ncZ.6k0dFmAEP1iyv8VTWZiA4tHQheBaB.zcokOYoqJ7c 2LAbfRWMDs.zbSB168RY40vfvXzcKVN1tpPRfakk8yn3fgn6Oi6ZEllX0tXWeNXG2CH8T8rvtBtT FH78nEQgNeGS.W0H5647tPLqWPretyVlRAOOnDh5CbbZuZbAoz1hcTh6pHJ83gpuP0TC9PdFJGPK JUMejrrC9gR.mnS4M16XSFxYESDlQpWoDetwh8bJnYjKOwbV9EJkrar.K1WNZWL6qkg95rlIV8JF 2FDyfV8MYBdPMzRaByoXp8SlO89Vt4VJ9huwHDYKZ2WMv67YzE.64adX7VBayb1D_rGnhcc2TlnM lGCbwUPUlTxTX9t_68gN0PLxuAiGw.MbVY2JOjVDCM2Pcxcr1E7qPkpJ.H82lLPMo33tRkhdorCs vHu4wiN9PtTQIhg8t3b7lpfftkw.EJyZaBEKmloyoasU.k4RduOfTRDT_6CC8ZcnJbdLZv.ElZFt lsGBtYTQrLn64ce5UhUo1dn0k6mtlwqJ3Jq0Ow6mXyXFQXFwCGnhf0QBabUvF3qVemglq6xpNMTi jZnYRbpgy5zUlVWM2TrmUQ5WpBppsUUkauRXcasGJEmAYRvLaXy77kyredmPz3AeuFyMMzvXPGco Cq1PVM4Gj8oh6o63y4MIKg_oWBvro9Yb1Oq7XoqEcQuIafROULMTrutrvEXyUfOGvnJvXJkk- Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.ne1.yahoo.com with HTTP; Tue, 29 Sep 2020 21:41:49 +0000 Date: Tue, 29 Sep 2020 21:41:48 +0000 (UTC) From: Nagaraj Raj To: Pg Bugs Message-ID: <221078363.71188.1601415708799@mail.yahoo.com> In-Reply-To: <64062533.78364.1601415362244@mail.yahoo.com> References: <64062533.78364.1601415362244.ref@mail.yahoo.com> <64062533.78364.1601415362244@mail.yahoo.com> Subject: Re: ERROR: insufficient columns in the PRIMARY KEY constraint definition MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_71187_506182745.1601415708798" X-Mailer: WebService/1.1.16718 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36 Content-Length: 8345 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_71187_506182745.1601415708798 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Added three scenarios,=C2=A0Postgres 11 | db<>fiddle |=20 |=20 | |=20 Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | On Tuesday, September 29, 2020, 02:36:17 PM PDT, Nagaraj Raj wrote: =20 =20 =20 I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have som= e partitioned table with `inherence` and planning to migrate them to the `d= eclaration`. Table DDL: CREATE TABLE c_account_p(=C2=A0 =C2=A0 billing_account_guid character varyi= ng(40)=C2=A0 NOT NULL,=C2=A0 =C2=A0 ingestion_process_id bigint NOT NULL DE= FAULT '-1'::integer,=C2=A0 =C2=A0 load_dttm timestamp(6) without time zone = NOT NULL,=C2=A0 =C2=A0 ban integer NOT NULL,=C2=A0 =C2=A0 CONSTRAINT billin= g_account_pkey PRIMARY KEY (billing_account_guid, ban)) PARTITION by RANGE(= load_dttm); When I try the create table, it's throwing below error: ERROR:=C2=A0 insufficient columns in the PRIMARY KEY constraint definitionD= ETAIL:=C2=A0 PRIMARY KEY constraint on table "l_billing_account_p" lacks co= lumn "load_dttm" which is part of the partition key.SQL state: 0A000 Is it mandatory/necessary that the `partition column` should be a primary k= ey? cause if I=C2=A0 include `load_dttm` as `PK` then its working fine. If the partition column should be supposed to be a PK, it's challenging to = create a partition by range with the date column, cause the load_dttm colum= n chances to have duplicate if data loaded `COPY`. Could some please help me to understand this scenario?=C2=A0=C2=A0=C2=A0htt= ps://dbfiddle.uk/?rdbms=3Dpostgres_11&fiddle=3D5000c4602c16350d77974868e62d= c7c9=C2=A0=C2=A0 Thanks. =20 ------=_Part_71187_506182745.1601415708798 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Added three scenarios, = Postgres 11 | db<>fiddle

Postgres 11 | db= <>fiddle

Free= online SQL environment for experimenting and sharing.




=20
=20
On Tuesday, September 29, 2020, 02:36:17 PM PDT, Nagara= j Raj <nagaraj.sf@yahoo.com> wrote:


I rece= ntly upgraded the database from PostgreSQL v9.6 to v11.7. we have some part= itioned table with `inherence` and planning to migrate them to the `declara= tion`.

Table DDL:


CREATE TABLE c_account_p
(
    billin= g_account_guid character varying(40)  NOT NULL,
   = ; ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
&nb= sp;   load_dttm timestamp(6) without time zone NOT NULL,
&nb= sp;   ban integer NOT NULL,
    CONSTRAINT billing= _account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION= by RANGE(load_dttm);



When I try the create table, it's throwing below error:

ERROR:  insufficient columns in the PRIMARY KEY constraint def= inition
DETAIL:  PRIMARY KEY constraint on table "l_billing_= account_p" lacks column "load_dttm" which is part of the partition key.
SQL state: 0A000


Is it manda= tory/necessary that the `partition column` should be a primary key? cause i= f I  include `load_dttm` as `PK` then its working fine.

=
If the partition column should be supposed to be a PK, it's chal= lenging to create a partition by range with the date column, cause the load= _dttm column chances to have duplicate if data loaded `COPY`.

Could some please help me to understand this sce= nario? 
Thanks.




------=_Part_71187_506182745.1601415708798--