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 1lwcEW-0003dI-G3 for pgsql-performance@arkaria.postgresql.org; Fri, 25 Jun 2021 03:10:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lwcEV-0001Pz-FZ for pgsql-performance@arkaria.postgresql.org; Fri, 25 Jun 2021 03:10:19 +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 1lwcEU-0001Pp-S4 for pgsql-performance@lists.postgresql.org; Fri, 25 Jun 2021 03:10:19 +0000 Received: from sonic306-22.consmr.mail.ne1.yahoo.com ([66.163.189.84]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lwcER-0005AL-Rw for pgsql-performance@postgresql.org; Fri, 25 Jun 2021 03:10:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1624590612; bh=oVbJ/KrdowBcaoUqDF4Qbdhkn2zLnDLLv/LqGAD9jaM=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=O6DLCFlJHUd5GcPY+/3JfWL22BieEcLUxOySMZyUxAPmCvFbvLvdY7ogLqIR+Eu43s6bFjFfTSgGK6WtBnXKocvrjF7Y8km2awnUp6R8bDSfLtGfi1FAh7FIUmh2UF58nwVG6B5O/SLdKRQUQJXiP6TPE2facRwEswB/jXrBJFpsvQ40vNfn6nvUqlnrmuPReK6ODXYOB8thJPMlff4Y5e8AcvUhYpBvyHIjxIOWLhi3Ix7oiqebi7LPuYf0dpqva6MgHRWqIjEyzFcxYRVRncqbZXdCl7PmoPK8W3S+HsIoIsEimzNrkpv4qP4rf5/W1VW4LTrqwXqeNs4mbhNOqw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1624590612; bh=vC8hxiz9FHdFYI2Q48+do0GIO7kK6j2TstOlmIA8h1T=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=VrO0rezCAU8MoH+uMeQ8Nuk93vGEo1c2nMffZUd9HJ6nYUNJDtclsVDqMUvGq3t0wBcTvtQF+r68/gGTHbbnxAFyTdCAIHlnAageT0aql7nV7xMWqqK6dnXnFf9VxiOXgQ4CcB7+PQ3vR1nI0iUxwm+LGwq1PpbCJHa2aYdTdPXRgd4zAtNRS4WWeyElF0ES94h5mGUOY4ZGZ24qwbYGXoTRBs7Q1EBCSfZXi0RnHkwQIVlCbsFO3woVZ863pjDC2NB/s14WEwBFqGe5AohAFOJ7bMKqqPe4jRuKK5S7DHh+AzF+LhcjEPQqrhE8RwbTVK9DiJSclhxsfjLlQBR9xg== X-YMail-OSG: g336EksVM1lC_8_1KMVLS4PmUhvVIBlC.AzoLSIGGRzcViKtvnBTsuM6uFxKzTm kP8J1Fn0PA3TwvceuVwXTstQqPLG5CC8lTWiczVE6PQ6gHsf8Qy6RYkHvJ2p2WxVQYbA8eSWQd2S LwBTOuEnYelfK5RLhspEE7GtJQj81cJYEBWMJmatGL33XyadVJ4Zqupi4.ajJYItGxyUYqcS_axM kp_LRfgXblHV9LAgUZYxLbyX.4trmtpoDgQciZmlkHkZ4NzG54bydV.UVt.wuASBTIBKToM9rqoW XgP7nqeFh.Yv40eXCV_lB5Ito9TmOGr6ghuHXezIoDawsvwn2AIBNr5mRift3h2pGaBvUzz8kVYs j2eIn9YkjVx55wfp7JT4doRV4H4uwjsuU.GEePARZoBa4eL1ljhGRuWt3JiVEhC3G_OqPAym7RI8 _veCPjbqmNmijKrGprrNoOtva03RCHbQ9ZM3fqD9cAnw3qh1eTCC0ty3dmqP58k4J8GOG4pyQcGS ARG1j18XC60dyZfljcyPCIF8JmtXBsYAOFS7yzt05bKYPfSap97r6C3F51GJcI4e094qQnOwhKov BIGfVDFKjuaZ4OJa6vg8LKWfv2_3RJd47CByd1AvL3Iqupg1ep9EaTFAktMwAeHqvLTd70HgnU11 4I1t2GSSnDppKmZkuF6b871KX6gKO.59ZcuFsIV12XraOK5NevQBitgUMd1RO2xwl7tYa0k36w9M VPO3WpeCaU4LBX22W9Ql.5dHBi98.Ze2rXiEzTRxYRch4zCMV5n50C0w4lNw0WVH8vySIRv4fQkX t.eoMeNeH2GyAH_V9xT5DB5T6tiFmIiDvno7daE0sfotWxYVGPAWCIMc78MyR3kPadS0vQC4hMp2 T2GmJqhwDKptqUNkq_0gnmzJqqvOnB4D0RASnbsqm_Zr5KJ_hzIeJCFTHKErtx2xJOF_qGFBckmS wKNj8RwCn_4C2C6mNaX4Qom0IH2Zbv_u8PO_6xblTYqiWfg.iMV907WC5dujl6Ma4XFH7l_8_Gib Z0TG2rRgcJSEc0KNjIvZGRgEH18FVUo__z80ByxoNpClxI8QxA7asRD.elQtLNi.ABe32PANqSID _1oltdQ2ipyxwo9pYLXx85XeInMPS9kgsQgakQ4Qw1HgRhrbw5h4DFyrxf19au33IgWnhQo7jMfg Vhx1MWeTNdSE9KlJxmydl_Pm5PRbRD7bJ8fZFfB9icWpH4x6bTVLCbWQGdFftr9n8zQbNgZtoD_R 9f8Mp_zkVkVbZZYZS.0OOomvkqAjAvy5WzTZx71rQKryQRaD.a6rw8.o5ht9oy71uS9k3iiXyRdL xf5FREG7EnmAchUOGIENvcR08ahSm8YGjlxOWq5MOFgF7h3uUCc7hpD263UO2oz3s1FpQ8hm_Xrn YqUv1oRXpWzHzfTS3s8KqUG1VPb4mF4YjhThoi0ZwE4PlTo8aCt_f643gPkUJvwSeJvWP1tkervI 31eJTMGjOvaMRNy1D26bEecBvh2EgGVEt_Zvk4a3u1qsshRHHJOnFblzHzrxR.D24mAXiB_coUOD ss6RuNTuGqgofoReCNZxNAaSwM97ABLpJHvHNi7HyCflmsr0uHeQBUL9UlEg5UGBs3Qs__0eB1QU 9xCksm2SwidE0Q108gRuOxqvW9TWI8JSIVaksd9me7O9jkr_TSFJWQ2pi5_Qu3yDjAXhcdeIdoNb nasrb1uj6uP8kr2ZDnQp9E13XfEu_tZOisH6Qp.Qxdm8GhT6OsYdi2KTJKV8JGza2MHQ1B0Ea8Xv oSLJJ5K2dF1eZiQbmJTd8KhjPvUJKZ32IfNtB95PiloHrwvI_mGRu08uDPMe1p7aYyGLWWGhVOvk Oo0peIjX4EUL55jlQxbDagCB..mtYB422v.1MtnNcAozd85RRK.TH4Bfe6jUN8dQePIarN2ZOCGC r2tvYxJxL_aeQjl8vf2bMo0m7Ox_r0VNjKVFeKAyrEWgLZETeDs5V9RXTEQjy3UoZFeaw.HUj7X_ kaQ1w3c7XYRmjBJhqRu_rpDmkCOXN4eTn2K5FOE4JEgyx0bmpFJZ1OotcnxsBUuwJDw3svl3OkMJ OlEimb0YsQzyTJqzyIyResy9aBTHXgPZaspS9BT5VE4671Sb4i9e9bsdiXidnUaFwlBD4px5VQJt hbgsflWvANwJoqqIaHwRPsdg0n9QI92rgcr90yYiz59AqVH_opuF6IEQOwqfafDq5He75E5Slua9 8L41l8SCjhTX44wRtYXSk0lXnzl5ZuQy978S6IrjTbZ0oOfKIIf3z4QsZdy1amfNtcUxIxee1qM8 XINE3vEP1DUKzw3VtxH.tS30TmlLyeBiN25mn6AdV3DCzC.TE5Y8cyC5UB9rnHZNYCs62JjLcexM JR_O3q0tEXCWJsdGe0jZJJOiuSA-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ne1.yahoo.com with HTTP; Fri, 25 Jun 2021 03:10:12 +0000 Date: Fri, 25 Jun 2021 03:10:07 +0000 (UTC) From: Nagaraj Raj To: Pgsql-performance Message-ID: <836265572.4593017.1624590607279@mail.yahoo.com> Subject: Partition column should be part of PK MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4593016_1369604189.1624590607278" References: <836265572.4593017.1624590607279.ref@mail.yahoo.com> X-Mailer: WebService/1.1.18469 YMailNorrin Content-Length: 7798 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4593016_1369604189.1624590607278 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable we have some partitioned tables with=C2=A0inherence=C2=A0and planning to mi= grate them to the=C2=A0declaration. Table DDL: CREATE TABLE c_account_p ( =C2=A0=C2=A0=C2=A0 billing_account_guid character varying(40)=C2=A0 NOT NUL= L, =C2=A0=C2=A0=C2=A0 ingestion_process_id bigint NOT NULL DEFAULT '-1'::integ= er, =C2=A0=C2=A0=C2=A0 load_dttm timestamp(6) without time zone NOT NULL, =C2=A0=C2=A0=C2=A0 ban integer NOT NULL, =C2=A0=C2=A0=C2=A0 CONSTRAINT billing_account_pkey PRIMARY KEY (billing_acc= ount_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 definition DETAIL:=C2=A0 PRIMARY KEY constraint on table "l_billing_account_p" lacks c= olumn "load_dttm" which is part of the partition key. SQL state: 0A000 Is it mandatory/necessary that the=C2=A0partition column=C2=A0should be a p= rimary key? cause if I include=C2=A0load_dttm=C2=A0as=C2=A0PK=C2=A0then it'= s working fine.db<>fiddle If the=C2=A0partition column=C2=A0should be supposed to be a=C2=A0PK, it's = challenging to create a partition by range with the date column, cause the= =C2=A0load_dttm=C2=A0column chances to have duplicate if data loaded=C2=A0C= OPY=C2=A0or=C2=A0INSERT.INSERT INTO c_account_p SELECT * from c_account_p_b= kp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stu= ff..' If I receive billing_account_guid, ban combination with different load_dttm= then it will end up with duplicate keys. Could some please help me to understand this scenario? Thanks. =C2=A0 ------=_Part_4593016_1369604189.1624590607278 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

w= e have some partitioned tables with inherence and planning to mig= rate them to the declaration.

Table DDL:

CREATE TABLE c_account_p

(

    billing_account_guid character varying(40)  NOT NU= LL,

  &n= bsp; ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,

    load_dttm= timestamp(6) without time zone NOT NULL,

    ban integer NOT NULL,

    CONSTRAIN= T billing_account_pkey PRIMARY KEY (billing_account_guid, ban)

) PARTITION by RANGE(load_dttm);

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

ERROR:  insufficient columns in= the PRIMARY KEY constraint definition

DETAIL:  PRIMARY KEY constraint on table "l_billi= ng_account_p" lacks column "load_dttm" which is part of the partition key.<= /p>

SQL state: 0A000
=

Is it manda= tory/necessary that the partition column should be a primary key?= cause if I include load_dttm as PK then it's working f= ine.db<>fiddle=


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_d= ttm column chances to have duplicate if data loaded COPY or&= nbsp;INSERT.
INSERT INTO c_account_p SELECT *= from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO U= PDATE SET 'some stuff..'

If I receive billing_account_= guid, ban combination with different load_dttm then it will end up with dup= licate keys.


Could some please help me to understand this scenario?

Thanks.

 



------=_Part_4593016_1369604189.1624590607278--