Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ueVBf-00ARmd-GB for pgsql-general@arkaria.postgresql.org; Wed, 23 Jul 2025 08:50:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ueVBe-0013k6-5q for pgsql-general@arkaria.postgresql.org; Wed, 23 Jul 2025 08:50:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ueVBd-0013j4-CD for pgsql-general@lists.postgresql.org; Wed, 23 Jul 2025 08:50:54 +0000 Received: from sonic314-21.consmr.mail.sg3.yahoo.com ([106.10.240.145]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ueVBY-000OES-2x for pgsql-general@postgresql.org; Wed, 23 Jul 2025 08:50:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753260644; bh=oaxXneVpP1H9tjbQ+yNelca5tWpBQ/P/ravN5/9mwCU=; h=Date:From:Reply-To:To:Subject:References:From:Subject:Reply-To; b=J86WWSEWuk6BRgm8CzJvpxMvak1Qg/pnSjKe9gwEqZUucWFClt8VT5qB03MwHvMHAFoncX+4G7hNK34bpXbAsuxJkoZVTuDfpphDY8Qm8e6fGHbrHtXH/xxZjU4r5t9bKJ5h+V2Dx8W31pOIrQ5X2pqIv7jsxUyrGlPGD+1ElnOBP5ms/mbtZNJFGu+pPQyosFHObyFuGIRJ20c5WSWz+MUsNFundYyutB1epSjoxCNerLoKD7HvlxZT/97PHuhPjUC/p2EiGeyb/UdT7edpC4lc7aj+//dI8StdEo947sOPzfi4jfwKxNbN0sso/5o8CwiJsTV/Ppr+W3/ULYau0w== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753260644; bh=a+8YwzOsmPkrC/ricKJ53lHfZxKLGbhdP0SXj3wRlwh=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=PaaMQSzk9MdijPFMN3WcbAY0U0BrXILHjA90PIb5wJ4yC/bx0lJ57b3QQUSHqWd7rxODL8Lm1oBcjBCK0zn5Qwz9MgTNJmaE1EsnEdJ8HF+YGeRslRnEPrlDz4SnpfUJ6kZTA08O9/LVpmnaO+u7Zbi4qUyDwyhVZgCJHVs9VRzoxY59Sb0KTriorT9yhXP0h4VsPBZARnuNIot2uqOCARzdgEa9cSo1PecKsC8jvFOg0Pa7Fed4W7+zdkBtXZrpvJrxBbCJeKtdVARP7QWqAJjFTrXUjAo+9aguIP3lnx3fEDmsYKXc28CLup7A4kuvNBUwjlqjnbGNKuXD0UhKuw== X-YMail-OSG: 782jDNUVM1lezanOQUUx7LxRCT12Wta.xH3HGwe4YOzrsE3e1SK91unI1ajjx88 OiJzTkTOjeEINwL.dcvyqu4XSV.tJ4xNPmzZeavTWOrKx8Yx4iHAuZwKP7vZK3Jj45sozNR4tlBh 0brSbNWH5ToRnvu8RZMlZiHkog_71eaU3aAVfTgZZO1gbcUEHuShrsTsW9HcxE5FyCgGHKh4WtQ_ feDjhmzxkNjoqbf1WlH3FuuV8jE8mEgu3jAE.yi_IVsnV6tDHMh3Nyisfn.LuzV3j8NWILI78.F4 XmGnL1DCj69i69evf3SYMKP481VPKL3wZQrXUqQgL.E0gONIO_zfgXFf56DXvBcjNuBaHTyYD6n6 PPzZVGdZ8lEhb8z5HGLGPokNWmluTLp37SR7kWWeapArDIcwZlzIi9LhlODVm4xNYbGFzS2gMsvi v4nNREcer6lrwJ2IcsWFF_hWKmTHXyk1WGe_yRptUGckFWv32kgPqlaieKrHe.wEfSsZDIaUCsJC wSOkDFcWzhV28tiZ719JH80k1zFGRBVEwCWvIoCjJaqg6Dfzewzfpf5F4NhUjxNRDCUQMiwfo77S QsW5ZF4DKMgyoqm8u0ra2UP4zg63CbmZjAtePHL2TfBbU996f7x7d0EXmbHzR3pWGz8gDk1pal7I Tm5bGQS.cvN9zdGyccFJ4WWcCYfF7q2UsaxQIphwfrOn8BlaABJCDUa2AHQiLZty57tTisBoqxhv EY8QhTR6FhmYL0WQbyLoKhfEversMHF9YTrxesXtfNrQPZyeBqmY5eEu_Q.sXx4j7nzE3Dllw40j B36mGqQNlz526qKwJycPfuQzgfDMclYbDBzmHGhozaE1nlDZricPdLhxslEYY0BRvR0GvPBXoznQ SJcHv5YNCKJYtqDpsZnR5sw9QwTpmOAlBqfqkYpS4GPY_KsUDS5gzgVSHivIU4CVwh27j6S2LsJQ Qs.Vs8uvbsGAXzMNCq6B.zYt18eML7hHp_mxUm9OTqnHFz2zuRZRffjcRwrRIdSMyhWEiYOhLmBu 1ax6jjnyGCyiVs8XA5mXARaDDBRAoE2d4WNTtwAS8qM7W53e.9mWzYfijviliXjXwyi3zE1_fSFQ 7wBGxZr3lvO8lV.o8IUCUmpjIKxCpCx4zS92RmGFTagec3nWo.BoNstIn7GgN3UItBO17lFrSG1T 4LIq7UwMLP2BVfOg.chxF1yE1qTXfiJzbyeOBp8eBnMIvL1HmDlWKD3Nvkje57c0bmpuMrgzu_Qg C1QhPbxfoRDHWibsDBGFnUSRXLQhG3_U64htzBguDddErrV6jJFfWXUnSBPkjNRC2HR_44b1AlM0 4b6_eOae800WKtY2yaAYYaJskP3YzPILHxLuXaTAwW32Jb5gdyGW9.mfurg6W0GGe2_fcFG5czEH MiC94g12e9pfdCRQcEOG3_VC1OnsMePOR7sSkILLdqIXDt12bhuGBDpNc0rk5QKghmAHlvYXJGV4 vCMJzDdh8bN0tt.U3iKA6wHU5F2e0iytXSr29Tihmq._3YsFIl5176Fr8edBZVjiNjGYyiuANvb0 TSGo4x4LTySIIIp.lENouZdmHK4T7vdqcRf.nXRENDgSp4ns.O9ff85R1sEVpQWnSeoXSJ1pubgz mDAZlmQeVPcVGa907EhfBJa9SQgtg92QrohO0g_S6bwyjp4PreC8ZInRu5KQ72kNtApBrESxQ5a7 uQTff94_maTHbOeIHuaaBVFDklH3zS_ecw6.G4Eexe5i_adD5UDFhNID3BF5NoQu0WweG3uLboCI Gzt3D0JQJv_Hp4t4ALox8oQhxCcg1z6xOg4dB_zsQ1lNJBuic6LqSMN2tP64DQU9fi29xpXA2Jr. wum8KuPCdPMS9mYxDguMeSfW1O.LKQmU5Agbwd0BKl9dlH8hz2m2nACN2huTyPz0DbYxtHWflGNW 9TvxynOzmDEhsFxEepSgQRH2KK085TYCfdWGbCI2PJKi11c0_TH2.B_HkOGtnX6X5f6Enrs7J3yq hhLsTX6Jm9_bXk7KjqacNnj40D.cUF8Cq_D5oCQx4NYbFMYGiS9REijJsjGTwPSVrwqltCacQJe9 D1qscbXCwxgHBFCYU3y11PPu4vOgfAkwPiwMP5rF9jHuJ7L7YNa2VHBzBtCkN4dY7Bhs2X2QNIOe py0OiltywhQRcRn_4.1rmLhYrI5EHoYdKlzb_sOqY4pDdCZpgxxhSGPAYKZBsh1IOUU.9Kw59iCg U4rGZ4xlBd5JouJ6DW3YmX4MF6yU_RAy6O09kS3vN0b94aQS6QLVBLxzM7P_lAFgeBJJhwayBzpQ Npg-- X-Sonic-MF: X-Sonic-ID: 9deac7bc-9315-491f-ace8-9d6eb9d976cb Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.sg3.yahoo.com with HTTP; Wed, 23 Jul 2025 08:50:44 +0000 Received: by hermes--production-gq1-74d64bb7d7-nrjbm (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID b49db5e8d79f0c999760acd37f6099d5; Wed, 23 Jul 2025 08:50:41 +0000 (UTC) X-YMail-OSG: gDxVLycVM1m5hJ2KzL6iIV7m4PfYT3JK5h33D2PDqDRlxhK3ky1p03aHe3DBDRH A9od9cL63URUUQwcPVPlgfSGvlVBUY7m4T5TQqU8B71EPY.RYGRyb28fFVORteICO1W3OZ.1tyGm RotBinFfgfuvvPhIaFNvKqG5mA04Sq3OMjV5rCWuEgIVp5gbPDNLxuLdgDbEIykZiB0wxglZ5u3t fjjba4iLQhCLhEW.jPbEOLBbliut1XVOe1eT78GMUPDRpO8codj94_PLrLCyFXUp1XDzY8Uor78Z rm.XV5nl2jfomZy1I6i6N4Cn0yfuEX3B4WPVFuqj5zPie3l1eATZ1J0ocsWG.SgueV5OerDiMctD bBW3b1ibHR3vCrp6_LUkX2gD3PJscxdeieT4jlT.4ZDdqulJaX7toKIb7IjjoxzwxX8kpZmH46PN khGFjWNMpjIRjotGOnJVHjFmHzu41QdBO2sECbxTu4T9csg1Q4OvlR1vxtzxW0Cr.Jtkom93Gr4d EfxCrHOigxXPNkbnba2cTIrnMBTtBT6tku7.hLJ13ZFHocTISyOk6uTv.76siwguJjwYVzG7eSD_ 3kDMMmk7RNPa8nPsQD8FfxrdNvxiUUIxcHbkrFPQeS2v_i9huDh9QAAJtrijjy7c4ACPPn4Vjt_1 21jvSf7RjynP77YENd1VZIUrEK8XrS6hrwLNKskRVNqlzTSbSwm6O8tdeaSduHDizER7SaxyqPx2 4UJKc7AByuG0myFGc8Urhq72RXyF972M08qkQ0j508oqRQ.3gp88Ac19QyU46GZyOPiU14vZwc5G Tx4m5l9Zr7QxzxqnKCihGAtDidnGaATqPyxi3ekren57YPnBKiFF3kgAQSNz8Z1UfeXqzoejP36R 7tAd0SFP_DxirhrEwT97t6TTDDmbkzWYd8rvdQplD1rw1mwcacPIYDqVs68OIG_af.dqzQmnC.iH ZnwjlHQuB8Gb5taYpiXNFG.csHuVaIKP5rM63FIGokah05mMULltQtFR_0jpI8AtfsejMcevFMvf ddIu1uIL0H7UcDrz8wZ6Wmv13KZIAU862ICFi.t8aEWxuY_Nw0DLyVqS_seMpnnRzYmQfUfL3I5h A0qJTi562Ajz5y.590ZYnlZZkQmT9PWoZhAzcGx__WBgdvvgJSJ8nNv2KxRErboIIMxwNSD1O.qg Gc933uIz5G9PhrFjx0qBXdAWhskncqoCj7ZbdWt5c8ZOji86W_c9LzBnFYReRd.3dyikUgXhXHCR q_RKckBromDuVDXpS4SSLyZnK0_WRuA0TL_H3PFnKPqsrFomVGs3EKc0G70dl1iZeYjESe.SLqz0 c55SM0InKefUgtTZrp0K.Mhjd0xSRYK0fq_Asxu3MCv8O4MGIzTw7Wju_JMWm.wzdJKO4SkfY9sd unyw9LW340xCKCfWGieKxrlb00Th4.3KxWd1mwAfivWobLqm62fJIeJB3hDARdI95MX2_5SHpDOD P_Pz3tfW_BZmseThdJuhTh.TLiMK7segpMcRXFGFaFeP7ulbHt9bcP9zIJgwkLGcLXLdhLW87MH7 8DSjlrplPsSLCDSzJjIWOE9mF0QHSUymuHr_.9Jc_ZN08S974VruhYjpkNiWhLobQ18X6bYdFxoF eTAhE4N5m9ktV_huBSeMciZyEb9X7huIhKAEw0phS.AVhLqbhb7ntuV4j7VpJ5mpVl3J5CU7A6TQ 9V._Owlr_7xQMJuAAOspznDxrRWBh3zyJgVwKeSBVdtxN.ix3hksAqIgzIBxUOvgmlwWZy_w_4yc JLQpDSB51eZZYTrLcCJBlDus23DkTY3LTJzwk1.Omw_X7L16pHpmKEkO5V6YzjsYmcNn6R6kfZkX ON12sSy0Png7r9_bVbFsHNGHULgo0o2c4mgTRj8qBkLPnze1n2MdDYOqgGDdVZcBEvbo8kCkCGqH 3uOE6eFy7Q8T8Zt17mkIsGCx9SDWPePNhXLl1TVql5K1E3X72u4WP9VmTUv63gl_6BCdvQYdx9Z6 5A_B21wCvvbUcqsRo3Ui.t1COYG4.xqbZ.n8gafGBaUEqTT7g2UokUMHRwy.Dn7xNa8zF3E8a8sl 9nEGc_0577yP9dWzRR1shMk0aeNyUfpDCGpMOQj4qkU4gSyqUOdLleJxtJUDy5Th2NEGMhBGhO7q E6HbElRovM8QZCHolMQJYpSjP_ofT1IZVn6RoZlgZUb2fySA7ZbADqefsf1lcT9q83NyFqQAWbsD 24Ob.ArqP_h2n6TN.DPgYHOkdfJzcsr8QqEbb2NJIL2aw.L26R8M5QzWNQggyvjX_8vod_sW0ZoN KzpNHBYqplSbvGGI73WOignj7 X-Sonic-MF: X-Sonic-ID: 1f055109-8b4a-4486-8e49-73082e2f4a90 Received: from sonic.gate.mail.ne1.yahoo.com by sonic320.consmr.mail.gq1.yahoo.com with HTTP; Wed, 23 Jul 2025 08:50:39 +0000 Date: Wed, 23 Jul 2025 08:50:37 +0000 (UTC) From: "sivapostgres@yahoo.com" Reply-To: "sivapostgres@yahoo.com" To: Pgsql-general Message-ID: <1453510076.1900935.1753260637232@mail.yahoo.com> Subject: Re: Is there any limit on the number of rows to import using copy command MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1900934_870082308.1753260637227" References: <1453510076.1900935.1753260637232.ref@mail.yahoo.com> X-Mailer: WebService/1.1.24187 YMailNorrin Content-Length: 24535 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_1900934_870082308.1753260637227 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 Here we try to transfer data from one database to another (remote) database= .=C2=A0 Tables do have records ranging from 85000 to 3600000 along with smaller siz= ed tables. No issues while transferring smaller sized tables. I here take one particular table [table1] which has 85000 records.The table= got Primary Key, Foreign Key(s), Triggers.=C2=A0 Trigger updates another t= able [table2]Table2 have 2 triggers, one to arrive a closing value and othe= r to delete, if the closing value is zero. 1.=C2=A0 Transfer the data from source database to a csv file.=C2=A0 85000 = records transferred. No issues.2.=C2=A0 Transfer the file to the remote loc= ation.=C2=A0 No issues.3.=C2=A0 Transfer the contents of the file to the ta= ble using Copy From command. - Fails when try to transfer all the 85000 rec= ords at once.=C2=A0=C2=A0 Copy from command is Copy public.table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMITER ',', HEA= DER TRUE) The above command succeeds, when1.=C2=A0 The trigger in Table1 is disabled = with all other constraints on. 2.=C2=A0 The no. of rows is within 16000 or less, with Trigger enabled.=C2= =A0 We haven't tried with higher no of rows. The above command goes on infinite loop, when1.=C2=A0 We try to transfer al= l 85000 rows at once, with Trigger and other constraints in table1 enabled.= =C2=A0 We waited for 1.5 hrs first time and 2.5 hrs second time before canc= elling the operation. I read in the documentation that the fastest way to transfer data is to use= Copy command.=C2=A0 And I couldn't find any limit in transferring data usi= ng that command.=C2=A0 One could easily transfer millions of rows using thi= s command. Here are the triggers. Trigger function, which is called from Table1 on After Insert, Update, Dele= te Declare variety_code character varying(30);Declare company_code character v= arying(10);Declare branch_code character varying(10);Declare location_fk ch= aracter varying(32);Declare opening_quantity numeric(17,3) ;Declare modifie= d_user character varying(50) ;Declare modified_date timestamp without time = zone ;Declare modified_computer character varying(50); BEGIN If (TG_OP =3D 'INSERT') Then company_code=C2=A0 =C2=A0 =C2=A0 =3D NEW= .companycode ; branch_code=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D NEW.branchcode ; l= ocation_fk=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D NEW.locationfk ; variety_code=C2= =A0 =C2=A0 =C2=A0=3D NEW.barcode ; opening_quantity =3D Coalesce(NEW.openin= gquantity,0); End If ; If (TG_OP =3D 'UPDATE') Then company_code=C2=A0 =C2=A0 =C2=A0 =3D NEW.comp= anycode ; branch_code=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D NEW.branchcode ; locati= on_fk=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D NEW.locationfk ; variety_code=C2=A0 =C2= =A0 =C2=A0 =3D NEW.barcode ; opening_quantity=C2=A0 =3D Coalesce(NEW.openin= gquantity,0) - OLD.openingquantity ; modified_user=C2=A0 =C2=A0 =C2=A0=3D = NEW.modifieduser ; modified_date=C2=A0 =C2=A0 =C2=A0=3D NEW.modifieddate ; = modified_computer =3D NEW.modifiedcomputer ; End If ; If (TG_OP =3D 'DELETE') Then company_code=C2=A0 =C2=A0 =C2=A0 =3D= OLD.companycode ; branch_code=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D OLD.branchcode= ; location_fk=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D OLD.locationfk ; variety_code= =C2=A0 =C2=A0 =C2=A0 =3D OLD.barcode ; opening_quantity=C2=A0 =3D OLD.openi= ngquantity * -1 ; modified_user=C2=A0 =C2=A0 =C2=A0=3D OLD.modifieduser ; m= odified_date=C2=A0 =C2=A0 =C2=A0=3D OLD.modifieddate ; modified_computer = =3D OLD.modifiedcomputer ; End If ; If (Select Count(*) From=C2=A0 =C2=A0table2 WHERE=C2=A0 companycode =3D co= mpany_code AND=C2=A0 =C2=A0 branchcode=C2=A0 =3D branch_code AND=C2=A0 =C2= =A0 locationfk=C2=A0 =3D location_fk AND=C2=A0 =C2=A0 barcode=C2=A0 =C2=A0 = =C2=A0=3D variety_code ) > 0 Then=C2=A0 =C2=A0 BEGIN UPDATE table2 SET=C2= =A0 =C2=A0 openingquantity =3D Coalesce(openingquantity,0) + opening_quanti= ty, modifieduser =3D modified_user, modifieddate =3D modified_date, modifie= dcomputer =3D modified_computer WHERE=C2=A0 companycode =3D company_code AN= D=C2=A0 =C2=A0 branchcode=C2=A0 =3D branch_code AND=C2=A0 =C2=A0 locationfk= =C2=A0 =3D location_fk AND=C2=A0 =C2=A0 barcode=C2=A0 =C2=A0 =C2=A0=3D vari= ety_code ; END ; Else BEGIN INSERT INTO public.table2( barcodestockpk, comp= anycode, branchcode, locationfk, barcode, baleopenheaderfk, lrentryheaderfk= , lrentrydetailfk,=C2=A0 baleopendetailfk, lrentrydetailsequencenumber, bal= eopendetailsequencenumber, barcodeopeningstockfk, sequencenumber, varietyfk= , brandfk, modelfk, patternfk, shadefk, materialfk, finishfk, sizefk, meter= breakup, unit, barcodesinglebulk, barcodeitem, effectiverate, openingquanti= ty, barcodedquantity, purchasereturnquantity, salesquantity, salescancellat= ionquantity,=C2=A0 salesreturnquantity, salesreturncancellationquantity, st= ockissuequantity, stockreceiptquantity, locationissuequantity, locationrece= iptquantity, branchissuequantity, branchreceiptquantity, closingstock, sale= srate, mrprate, labelrate, ratecode,=C2=A0 discountpercent, discountrate,= =C2=A0 defectiveitem, locked, insertuser, insertdate, insertcomputer,=C2=A0= modifieduser, modifieddate, modifiedcomputer, wsrate, reversecalculation, = hsnnumber) VALUES ( replace(uuid_generate_v4()::text, '-', ''), company_cod= e , branch_code, location_fk, variety_code, Null, Null,Null,=C2=A0 Null, Nu= ll, Null, NEW.barcodeopeningstockpk,=C2=A0 NEW.Sequencenumber,=C2=A0 NEW.va= rietyfk, NEW.brandfk, NEW.modelfk, NEW.patternfk, NEW.shadefk, NEW.material= fk, NEW.finishfk, NEW.sizefk,=C2=A0 NEW.meterbreakup, NEW.unit, NEW.barcode= singlebulk, NEW.barcodeitem, NEW.effectiverate,=C2=A0 opening_quantity, 0, = 0, 0, 0,=C2=A0 0, 0, 0, 0, 0, 0, 0, 0, 0, NEW.salesrate, NEW.mrprate, NEW.l= abelrate, NEW.ratecode, 0, 0, 'N', NEW.locked, NEW.insertuser, NEW.insertda= te, NEW.insertcomputer,=C2=A0 NEW.modifieduser, NEW.modifieddate, NEW.modif= iedcomputer, NEW.wsrate, 'N', NEW.hsnnumber); END ; End IF ; RETURN NEW ;EN= D ; Trigger functions in Table 2First Trigger, which calculates the closing sto= ck, on before insert, update BEGIN If (TG_OP =3D 'INSERT') Then NEW.closingstock =3D coalesce(NEW.barcod= edquantity,0) + coalesce(NEW.openingquantity,0) -=C2=A0 coalesce(NEW.salesq= uantity,0) +=C2=A0 coalesce(NEW.salesreturnquantity,0) + coalesce(NEW.sales= cancellationquantity,0) - coalesce(NEW.salesreturncancellationquantity,0) -= coalesce(NEW.purchasereturnquantity,0) - coalesce(NEW.stockissuequantity,0= ) + coalesce(NEW.stockreceiptquantity,0) - coalesce(NEW.locationissuequanti= ty,0) +=C2=A0 coalesce(NEW.locationreceiptquantity,0) - coalesce(NEW.branch= issuequantity,0) + coalesce(NEW.branchreceiptquantity,0) ; Return New ; End= If ; If (TG_OP =3D 'UPDATE') Then NEW.closingstock =3D coalesce(NEW.barco= dedquantity,0) + coalesce(NEW.openingquantity,0) -=C2=A0 coalesce(NEW.sales= quantity,0) +=C2=A0 coalesce(NEW.salesreturnquantity,0) + coalesce(NEW.sale= scancellationquantity,0) - coalesce(NEW.salesreturncancellationquantity,0) = - coalesce(NEW.purchasereturnquantity,0) - coalesce(NEW.stockissuequantity,= 0) + coalesce(NEW.stockreceiptquantity,0) - coalesce(NEW.locationissuequant= ity,0) +=C2=A0 coalesce(NEW.locationreceiptquantity,0) - coalesce(NEW.branc= hissuequantity,0) + coalesce(NEW.branchreceiptquantity,0) ; Return New ; En= d If ;END Second trigger, which deletes row, when every value is zero, after insert, = update, delete Begin If Coalesce(NEW.openingquantity,0) =3D 0 and=C2=A0 Coalesce(NEW.barco= dedquantity,0) =3D 0 and Coalesce(NEW.salesquantity,0) =3D 0 and Coalesce(N= EW.salescancellationquantity,0) =3D 0 and Coalesce(NEW.salesreturnquantity,= 0) =3D 0 and=C2=A0 Coalesce(NEW.salesreturncancellationquantity,0) =3D 0 an= d Coalesce(NEW.purchasereturnquantity,0) =3D 0 and Coalesce(NEW.stockissueq= uantity,0) =3D 0 and Coalesce(NEW.stockreceiptquantity,0) =3D 0 and Coalesc= e(NEW.locationissuequantity,0) =3D 0 and Coalesce(NEW.locationreceiptquanti= ty,0) =3D 0 and Coalesce(NEW.branchissuequantity,0) =3D 0 and Coalesce(NEW.= branchreceiptquantity,0) =3D 0 Then Delete=C2=A0 From=C2=A0 tx_barcode_stoc= k Where=C2=A0 barcodestockpk =3D new.barcodestockpk ; End If ; Return New ;= END Any (other) suggestion to transfer successfully is really appreciated. Happiness Always BKR Sivaprakash ------=_Part_1900934_870082308.1753260637227 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Tried in PostgreSQL 11.11 , PostgreSQL= 15.2 in Windows 10

<= div dir=3D"ltr" data-setdir=3D"false">Here we try to transfer data from one= database to another (remote) database. 

Tables do have records ranging from 85000 to 3600000 alo= ng with smaller sized tables.
No issues while transferring smaller sized= tables.

I here take one particular table [table1] which = has 85000 records.
The table go= t Primary Key, Foreign Key(s), Triggers.  Trigger updates another tabl= e [table2]
Table2 have 2 trigge= rs, one to arrive a closing value and other to delete, if the closing value= is zero.

1.  Transfer the data from source database= to a csv file.  85000 records transferred. No issues.
2.  Transfer the file to the remote loc= ation.  No issues.
3. = ; Transfer the contents of the file to the table using Copy From command. -= Fails when try to transfer all the 85000 records at once.  

Copy from command is

Copy public.= table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE)

The above command succeeds, when
1.  The trigger in Table1 is disabled with all = other constraints on.
2.  T= he no. of rows is within 16000 or less, with Trigger enabled.  We have= n't tried with higher no of rows.

The above command goes on infinite= loop, when
1.  We try to = transfer all 85000 rows at once, with Trigger and other constraints in tabl= e1 enabled.  We waited for 1.5 hrs first time and 2.5 hrs second time = before cancelling the operation.

I read in the documentat= ion that the fastest way to transfer data is to use Copy command.  And= I couldn't find any limit in transferring data using that command.  O= ne could easily transfer millions of rows using this command.

Here are the triggers.
Trigger function, which is ca= lled from Table1 on After Insert, Update, Delete

Declare variety_code character varying(30);
Declare company_cod= e character varying(10);
Declare branch_code character varying(10= );
Declare location_fk character varying(32);
Declare o= pening_quantity numeric(17,3) ;
Declare modified_user character v= arying(50) ;
Declare modified_date timestamp without time zone ;<= /div>
Declare modified_computer character varying(50);

BEGIN
=09If (TG_OP =3D 'INSERT') Then=
=09=09company_code     = =3D NEW.companycode ;
=09=09branch_cod= e       =3D NEW.branchcode ;
=09=09location_fk       =3D NEW.locationfk ;<= /span>
=09=09variety_code     = =3D NEW.barcode ;
=09=09opening_quantit= y =3D Coalesce(NEW.openingquantity,0);
=09End If ;

=09If (TG_OP= =3D 'UPDATE') Then
=09=09company_code&= nbsp;     =3D NEW.companycode ;
=09= =09branch_code       =3D NEW.branchcode ;=
=09=09location_fk       = =3D NEW.locationfk ;
=09=09variety_code=       =3D NEW.barcode ;
=09=09<= /span>opening_quantity  =3D Coalesce(NEW.openingquantity,0) - OLD.open= ingquantity ;=09=09
=09=09= modified_user     =3D NEW.modifieduser ;
=09=09modified_date     =3D NEW.modifieddat= e ;
=09=09modified_computer =3D NEW.mod= ifiedcomputer ;

=09End I= f ;
=09If (TG_OP =3D 'DELETE') Then
=09=09company_code      =3D= OLD.companycode ;
=09=09branch_code&nb= sp;      =3D OLD.branchcode ;
= =09=09location_fk       =3D OLD.locationfk ;
=09=09variety_code      =3D= OLD.barcode ;
=09=09opening_quantity&n= bsp; =3D OLD.openingquantity * -1 ;
=09=09modified_user     =3D OLD.modifieduser ;
=09=09modified_date     =3D OLD.modified= date ;
=09=09modified_computer =3D OLD.= modifiedcomputer ;

=09En= d If ;


=
=09If (Select Count(*)
=09=09From   table2
=09=09WHERE  companycode =3D company_code
= =09=09AND    branchcode  =3D branch_code
=
=09=09AND    locationfk  =3D locatio= n_fk
=09=09AND    barcode&nbs= p;    =3D variety_code ) > 0 Then
  &nbs= p;
=09=09BEGIN
= =09=09=09UPDATE table2
=09=09=09= SET    openingquantity =3D Coalesce(openingquantity,0) + opening_= quantity,
=09=09=09=09modifieduser =3D = modified_user, modifieddate =3D modified_date, modifiedcomputer =3D modifie= d_computer
=09=09=09WHERE  company= code =3D company_code
=09=09=09AND = ;   branchcode  =3D branch_code
=09= =09=09AND    locationfk  =3D location_fk
=
=09=09=09AND    barcode    &nbs= p;=3D variety_code ;
=09=09END ;=
=09Else
=09=09BEGIN
=09=09=09INSERT INTO public.= table2(
=09=09=09=09barcodestockpk, com= panycode, branchcode, locationfk, barcode, baleopenheaderfk, lrentryheaderf= k, lrentrydetailfk, 
=09=09=09=09b= aleopendetailfk, lrentrydetailsequencenumber, baleopendetailsequencenumber,= barcodeopeningstockfk, sequencenumber,
=09=09= =09=09varietyfk, brandfk, modelfk, patternfk, shadefk, materialfk, f= inishfk, sizefk,
=09=09=09=09meterbreak= up, unit, barcodesinglebulk, barcodeitem, effectiverate,
<= span>=09=09=09=09openingquantity, barcodedquantity, purchasere= turnquantity, salesquantity, salescancellationquantity, 
<= div>=09=09=09=09salesreturnquantity, salesreturncancella= tionquantity, stockissuequantity, stockreceiptquantity, locationissuequanti= ty, locationreceiptquantity,
=09=09=09=09branchissuequantity, branchreceiptquantity, closingstock, salesrate, mrpr= ate, labelrate, ratecode,  discountpercent, discountrate, =
=09=09=09=09defectiveitem, locked, insertuser= , insertdate, insertcomputer, 
=09=09=09= =09modifieduser, modifieddate, modifiedcomputer, wsrate, reversecalc= ulation, hsnnumber)
=09=09=09=09VALUES = ( replace(uuid_generate_v4()::text, '-', ''), company_code , branch_code, l= ocation_fk, variety_code, Null, Null,Null, 
=09=09=09=09=09Null, Null, Null, NEW.barcodeopeningstockpk, = NEW.Sequencenumber, 
=09=09=09=09=09NEW.varietyfk, NEW.brandfk, NEW.modelfk, NEW.patternfk, NEW.shadefk, NEW= .materialfk, NEW.finishfk, NEW.sizefk, 
= =09=09=09=09=09NEW.meterbreakup, NEW.unit, NEW.barcodesinglebulk, NE= W.barcodeitem, NEW.effectiverate, 
=09=09= =09=09=09opening_quantity, 0, 0, 0, 0, 
= =09=09=09=09=090, 0, 0, 0, 0, 0, 0, 0, 0, NEW.salesrate, NEW.m= rprate, NEW.labelrate, NEW.ratecode, 0, 0,
=09= =09=09=09=09'N', NEW.locked, NEW.insertuser, NEW.insertdate, NEW.ins= ertcomputer, 
=09=09=09=09=09NEW.m= odifieduser, NEW.modifieddate, NEW.modifiedcomputer, NEW.wsrate, 'N', NEW.h= snnumber);
=09=09END ;
=09End IF ;
=09RETU= RN NEW ;
END ;


Trigger functions in Table 2
First Trigger, which calculates the closing = stock, on before insert, update

BEGIN
=09If (TG_OP =3D 'INSERT') Then
= =09=09NEW.closingstock =3D coalesce(NEW.barcodedquantity,0) +<= /span>
=09=09coalesce(NEW.openingquantity,0) -=  
=09=09coalesce(NEW.salesquantity= ,0) + 
=09=09coalesce(NEW.salesret= urnquantity,0) +
=09=09coalesce(NEW.sal= escancellationquantity,0) -
=09=09coale= sce(NEW.salesreturncancellationquantity,0) -
= =09=09coalesce(NEW.purchasereturnquantity,0) -
=09=09coalesce(NEW.stockissuequantity,0) +
= =09=09coalesce(NEW.stockreceiptquantity,0) -
=09=09coalesce(NEW.locationissuequantity,0) + = ;
=09=09coalesce(NEW.locationreceiptqua= ntity,0) -
=09=09coalesce(NEW.branchiss= uequantity,0) +
=09=09coalesce(NEW.bran= chreceiptquantity,0) ;
=09=09Return New= ;
=09End If ;
= =09=09
=09If (TG_OP =3D 'UPDATE') Then<= /span>
=09=09NEW.closingstock =3D coalesce(NEW= .barcodedquantity,0) +
=09=09coalesce(N= EW.openingquantity,0) - 
=09=09coa= lesce(NEW.salesquantity,0) + 
=09=09coalesce(NEW.salesreturnquantity,0) +
=09=09= coalesce(NEW.salescancellationquantity,0) -
<= span>=09=09coalesce(NEW.salesreturncancellationquantity,0) -<= /div>
=09=09coalesce(NEW.purchasereturnquantity,0) -=
=09=09coalesce(NEW.stockissuequantity,= 0) +
=09=09coalesce(NEW.stockreceiptqua= ntity,0) -
=09=09coalesce(NEW.locationi= ssuequantity,0) + 
=09=09coalesce(= NEW.locationreceiptquantity,0) -
=09=09= coalesce(NEW.branchissuequantity,0) +
=09=09coalesce(NEW.branchreceiptquantity,0) ;
= =09=09Return New ;
=09End If ;
END

Second trigger, which deletes= row, when every value is zero, after insert, update, delete

Begin
=09If Coalesce(NEW.openingquantity,0) =3D 0 and 
=09Coalesce(NEW.barcodedquantity,0) =3D 0 an= d
=09Coalesce(NEW.salesquantity,0) =3D = 0 and
=09Coalesce(NEW.salescancellation= quantity,0) =3D 0 and
=09Coalesce(NEW.s= alesreturnquantity,0) =3D 0 and 
=09Coalesce(NEW.salesreturncancellationquantity,0) =3D 0 and
=09Coalesce(NEW.purchasereturnquantity,0) =3D 0 and
=09Coalesce(NEW.stockissuequantity,0) =3D= 0 and
=09Coalesce(NEW.stockreceiptquan= tity,0) =3D 0 and
=09Coalesce(NEW.locat= ionissuequantity,0) =3D 0 and
=09Coales= ce(NEW.locationreceiptquantity,0) =3D 0 and
= =09Coalesce(NEW.branchissuequantity,0) =3D 0 and
=09Coalesce(NEW.branchreceiptquantity,0) =3D 0 Then=
=09=09Delete 
=09=09From  tx_barcode_stock
= =09=09Where  barcodestockpk =3D new.barcodestockpk ;
=09End If ;
=09Return New ;
END

Any (other) suggestion to transfer successf= ully is really appreciated.
Happiness Always
BKR Sivap= rakash

<= /html> ------=_Part_1900934_870082308.1753260637227--