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 1kNNID-0001Ba-Mc for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 21:36:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kNNIC-00068U-Av for pgsql-bugs@arkaria.postgresql.org; Tue, 29 Sep 2020 21:36:12 +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 1kNNIB-00063I-PJ for pgsql-bugs@lists.postgresql.org; Tue, 29 Sep 2020 21:36:12 +0000 Received: from sonic306-22.consmr.mail.ne1.yahoo.com ([66.163.189.84]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kNNI6-00038q-Hs for pgsql-bugs@postgresql.org; Tue, 29 Sep 2020 21:36:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1601415365; bh=NKDjW6n5urFazks+Ki9xhJHNqMyth+Hy64m+wVB7W7E=; h=Date:From:To:Subject:References:From:Subject; b=TbZ6jBVLVq7z0MEiXh6qCSwurOF16eyuI9cuS7OZkCk3dttoTdcz8/Ttjfc/R8NEyjkNfjHi+C3m0QkS/2wrziDm7fmOI9yj/86qKwu4GocqH3ivvXXZcgozMjUi2gVYM7qqqYuIWFsGsKMl9nlxo6ys6AvSPfIkzuB8Br99jX3Kq3AwoFY37fguNoC69tYLUcNES0b0Jw7DyWvgqxE5qwYdbL+HVNiTL5UrgKPNBHpPVb/4aC/GfvVycCSvUArEAqViof+qMoqoXhZBxI3DXxsM/G8vAf3BwnT6Y8ZYtZU77FUjtKKQElu22eD1HBWbTmBKA28iOvBJqZJPMVttkw== X-YMail-OSG: 85LnXn4VM1noa7ooKFPCRWVK.R2WU1AsXzqd8SQGmM.LqiBAtIERDTh9RwTVReL .jX1PXBMs.50Cq1b0HbBPK9m.YPyy6OcYBrVY2a4wohhYeqb8rL51fpAx0WRM81Y4njuTnnvHDL1 uvY9pJ.NsZzPw_fJqb.mAmnMlVvjSW8hH_Bd2PKzvuSusdGqpxzlf7vCZ4RPSIqRjl7_2amgyo1z 5rtihX9xLppI1cWhXHXzzg1rOGyp_fcWvehHjDz2o7gEDH3Wo77aNWdyAP7bAkURVYobGdBGKiKu C5iQHwtZO11UGxjonarUojRpVIlB_FX4WD.IINWxLaWH.ktr0SlIHvh_QPK9J5uWokNyNfTwF4eC mc22wq.OVRhscuMDCoW3gSY3m8urFVSHL6xVucKJnjcyDyg2HT.PrCmopDhclFXqVIxyEcb6Y_M2 F05MJppcBwfuOQMKoIGvMIacfboN4DVFLhdp3lkJEtldJED0ngALIr7xT3BeDcTXCoEMwNoYGXxD rY4Xob_9xFj9URZVJbqkyg3gnZRTVIPt5msO0ptHChtPhclEDe0VdxHraQ_F7WImzRxNcFYHVgQV vLIcWVEj.SwVSLkK71YfB79wWpivtEmWqZ.4pEuheCeYOIJdWx_eBlW_obUZXC9HlQVEtbp_WGEF LuBf7HzMbvdC3CPsZU1tYxtPa_KLuLwhg5.JblmGCFjWLdwvQsfmHRIBWhDLCFYZSzDFJSa.uvYX R0aWpQz4nPDCnidRs8lm6EC_.jSPkVrIpRVtz9skOF6TCi3oQTCt36oQQqzo_jm9bZ0KouS.65U7 7gJQ40lNg2h2FudBYnJZXi6rQRps.RuOOuOk8rl9GaIda.Rtj939kLmOpwdvf77LUzq5AsSJi.k8 VH6Q3GaMh2mPYpVl3CXu8hIUwB7_.gdlu3zF_jv.s9b6o713P9LlucNA5co6_OQThcH1tu1RtojN XE2pmYaGNBfTC.yBLXdyTDIC0OEJTrfQn0gedCgvuKta9sF0WcX.gKOnf4YUbedC_XE3vEobiEWG GE5mLbydM8WDLnu8SuoBMPjXAKotLkx4mncLcSYgs_vIObEqZmn6J3I_VxP6JNFV7P0Bgbltrkyp Bq3lV65QIbQK6mRmW43sR1N9ocjCbMeo76cuUKhceuHpWYN.G5Pna9AIxH5kZ1YjqWu72Jy.VlmY lZ8Yib5Di8WSnoXvVyjFKl8kheCxXnsN16mIB147LK2XITfyz8swhNR5inhjqimLvhJuYCtLbpqj TQMmo89sYlHs42QE_YGSj5O56939hoO6_CcqFfUUh7TDzVlM9G_lOZw0vmNN2vrlmtjkl8IAyBJj F11.fIAf_gpBKUQNG_kLVPsHmvu7eTv8YqPEhzUM1OQya1CH6N49YDApIBXIO_7GlELdI9n4zEEh Spdoac_.9jsL2RVoa5gSBRg.i.uTJc9LhYc.PHfGQu6NVSWxwsRdRkhhkNyaabuclOyl5lMqbj.x yakXGl_mMaH4n_MOKDGlEi3p3_6ZoOoAF Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ne1.yahoo.com with HTTP; Tue, 29 Sep 2020 21:36:05 +0000 Date: Tue, 29 Sep 2020 21:36:02 +0000 (UTC) From: Nagaraj Raj To: Pg Bugs Message-ID: <64062533.78364.1601415362244@mail.yahoo.com> Subject: ERROR: insufficient columns in the PRIMARY KEY constraint definition MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_78363_41061894.1601415362242" References: <64062533.78364.1601415362244.ref@mail.yahoo.com> 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: 4585 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_78363_41061894.1601415362242 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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. ------=_Part_78363_41061894.1601415362242 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

I recently upgraded the datab= ase from PostgreSQL v9.6 to v11.7. we have some partitioned table with `inh= erence` and planning to migrate them to the `declaration`.

Table DDL:


CREATE TABLE c_= account_p
(
    billing_account_guid characte= r varying(40)  NOT NULL,
    ingestion_process_id = bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm ti= mestamp(6) without time zone NOT NULL,
    ban integer = NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY K= EY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);



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

ERROR:  in= sufficient columns in the PRIMARY KEY constraint definition
DETAI= L:  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 mandatory/necessary that the= `partition column` should be a primary key? cause if I  include `load= _dttm` as `PK` then its working fine.

If the parti= tion column should be supposed to be a PK, it's challenging to create a par= tition by range with the date column, cause the load_dttm column chances to= have duplicate if data loaded `COPY`.


<= div>Could some please help me to understand this scenario? 

Thanks.




=

------=_Part_78363_41061894.1601415362242--