Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gMEUq-000855-8z for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 15:51:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gMESh-00046b-8d for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 15:49:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gMESg-00046U-Mb for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 15:49:15 +0000 Received: from sonic310-14.consmr.mail.bf2.yahoo.com ([74.6.135.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gMESW-0001O0-UH for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 15:49:12 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1542037742; bh=ruC/E4pJfXFllA9wxxK8FFwIqgb/UqMvEeOX62AT7yo=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=uXmQ2C4gIFLycY9AnZBx9o1wkFtpFE3qWB36YL5aJ73rrxik8kd4jrOdvKOGh2K0yxwgGetVxRU+EyYhTRSaSCoTwlM7tWmRy732Jar8bTp+wJhxukD1hxRHCZCYRyTr33jt49ZVOhv9rMcu7/xnMzfn5iSVexZFMjNnGA09CgLkpmcPOkeUkezXwpA4duko3okB4t8PO+LAqv5CF7I3VLPaEq4vaSuzRNvKQENj/3CSp47ZYh+Hs+qb1CJx9wKzSt7h8I4DDtwrqVzIZcnGHYYjhNwHs+j6bwWHP63nwZMspZwL5y+IapW2/J4LwM4esHmFZWWiMFRD4H9Ha4cCuw== X-YMail-OSG: x.n2h4UVM1lsqp7tCgLd4Z9qIn6tqCey0j4RXHJdaSWrmKHN84cdTnrdhKDxSGD hD6qWJxHjOjd0lbjvpZ47BZUizVuIrfrYlcoDJmoaIEdR9h4iXy8a5P3Lw2LVt.pik1WLAA5bSbT vBSE7qO1TnXRbBZKcidfPeQreswohSoh_BZjmMdIbESJLNlJgxMvJBByEIr5F67tDXHs3x5L0wKj 08YxzfTaQZCkXeKKNwvOyll6d7WEe90eQmHo3EwOAmoS7B8yHoZ6chAugjBjLkYn1ponY_FTorX7 QH6IQdpirSepJoCWCNGzsNSXpdZc_A4J8re26pMsJYMfiAoAC945WKHqO_QWH.88pRq88DWhJAkL SAo0eGMPGzfnT05GO8QkVptyoRJLw_HuGhtWp_PyNDPHB0PX11taRzwtykj.5fDiG93X.62NK3xE KYoben5j4ScAljahnMZYyjpG833cfoncaH1gu5V.SNXW1pGaba414eE12rs1ysjrHcaih3VtS1vd k0AbGXV4BOu0uK_3VVgJZf0FQ26JoITgx4vCvzMdpb4vPJv6MAZz.iUE70VOyVkeBlnRQi07kaYI Qjy_esDEuULCEussqgQ7ZE36v9ZXUQOCaNmqL2fZ418JqGg6MXpf0W890dvXAjbSY_S9WENFmWTB iMZGs6qZVYvwenlfo7BpP3TmhJ4UASmh3LlVBBipQhQszdxA_Oyecg8HYL38C9w0wQBI.UYKAGkV Oet27TVxblSg4rrbN6K31riZCtlakK0TGukIntl0RDYMUVKLlS.pI8.FXc4.g83bAcEIUUQ6WdPQ .E_ZX.Cec.G6pcrdbBJtr8svVF5XZe5_ZjYVVuP8jjjxX8xbcBvJVljgb650K3tRikPb_IWf4oLx e8XK2SpD5Cb_YMBwjufahvlPGpw17VWYl1EBToAZta3rk3iH.SXGC8E4sY5JtZaENlbgfM_C7MXg TfWplaXvttRqWvVuBFDDG9U2_MVai8PyrPhtq3rvYTLUH_zSn2CWLibjC5tLEzU80H813N3g- Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.bf2.yahoo.com with HTTP; Mon, 12 Nov 2018 15:49:02 +0000 Date: Mon, 12 Nov 2018 15:48:36 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Guillaume Lelarge Cc: Tom Lane , Ron , "pgsql-admin@lists.postgresql.org" Message-ID: <1122299377.961941.1542037716174@mail.yahoo.com> In-Reply-To: References: <1425722754.312953.1541603425493.ref@mail.yahoo.com> <1425722754.312953.1541603425493@mail.yahoo.com> <12036.1541630162@sss.pgh.pa.us> <832768265.636177.1541631121047@mail.yahoo.com> Subject: Re: query can't merge into table of the other schema MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_961940_2092650866.1542037716164" X-Mailer: WebService/1.1.12732 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36 Content-Length: 48960 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_961940_2092650866.1542037716164 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I tried INSERT ... ON CONFLICT and still not work CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_G28" (v_Ret OUT int) = RETURNS integeras $$declare=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int;=C2=A0 =C2=A0 =C2=A0 =C2= =A0 v_ErrorMsg=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(512)= ;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 varchar(32) =3D 'PR_MIG_STG_G28'; begin =C2=A0 =C2=A0----------------------------------=C2=A0 =C2=A0--### MERGING: = STG_G28 into SC_G28=C2=A0 =C2=A0------------------------------------=C2=A0 = =C2=A0MERGE INTO cidrdba.sc_g28 prod--=C2=A0 =C2=A0USING (INSERT into cidrd= ba.sc_g28 prodUSING cidr_staging.STG_G28 stgON ( prod.receipt_number =3D st= g.receipt_number )=C2=A0 =C2=A0WHEN MATCHED THEN UPDATE SET=C2=A0 =C2=A0 = =C2=A0 prod.Service_Center=C2=A0 =C2=A0 =C2=A0=3D stg.Service_Center,--=C2= =A0 =C2=A0 =C2=A0 prod.Receipt_Number=C2=A0 =C2=A0 =C2=A0=3D stg.Receipt_Nu= mber,=C2=A0 =C2=A0 =C2=A0 prod.Rep_INS_Attny_ID=C2=A0 =C2=A0=3D stg.Rep_INS= _Attny_ID,=C2=A0 =C2=A0 =C2=A0 prod.Rep_State_Num=C2=A0 =C2=A0 =C2=A0 =3D s= tg. Rep_State_Num,=C2=A0 =C2=A0 =C2=A0 prod.Rep_VOLAG=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =3D stg.Rep_VOLAG,=C2=A0 =C2=A0 =C2=A0 prod.Rep_Rep_Code=C2= =A0 =C2=A0 =C2=A0 =C2=A0=3D stg.Rep_Rep_Code,=C2=A0 =C2=A0 =C2=A0 prod.Rep_= Last_Name=C2=A0 =C2=A0 =C2=A0 =3D stg.Rep_Last_Name,=C2=A0 =C2=A0 =C2=A0 pr= od.Rep_First_Name=C2=A0 =C2=A0 =C2=A0=3D stg.Rep_First_Name,=C2=A0 =C2=A0 = =C2=A0 prod.Rep_Middle_Name=C2=A0 =C2=A0 =3D stg.Rep_Middle_Name,=C2=A0 =C2= =A0 =C2=A0 prod.Rep_Firm_Name=C2=A0 =C2=A0 =C2=A0 =3D stg.Rep_Firm_Name,=C2= =A0 =C2=A0 =C2=A0 prod.Rep_Street=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D stg.= Rep_Street,=C2=A0 =C2=A0 =C2=A0 prod.Rep_Street_2=C2=A0 =C2=A0 =C2=A0 =C2= =A0=3D stg.Rep_Street_2,=C2=A0 =C2=A0 =C2=A0 prod.Rep_City=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0=3D stg.Rep_City,=C2=A0 =C2=A0 =C2=A0 prod.Rep_Stat= e=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D stg.Rep_State,=C2=A0 =C2=A0 =C2=A0 = prod.Rep_ZIP=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =3D stg.Rep_ZIP,=C2= =A0 =C2=A0 =C2=A0 prod.Rep_Province=C2=A0 =C2=A0 =C2=A0 =C2=A0=3D stg.Rep_P= rovince,=C2=A0 =C2=A0 =C2=A0 prod.Rep_Postal_code=C2=A0 =C2=A0 =3D stg.Rep_= Postal_code,=C2=A0 =C2=A0 =C2=A0 prod.Rep_Country=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =3D stg.Rep_Country,=C2=A0 =C2=A0 =C2=A0 prod.mig_filename=C2=A0 =C2=A0= =C2=A0 =C2=A0=3D stg.mig_filename,=C2=A0 =C2=A0 =C2=A0 --prod.mig_insert_d= t=C2=A0 =C2=A0 =C2=A0 =3D stg.mig_insert_dt,=C2=A0 =C2=A0 =C2=A0 --prod.mig= _modified_dt=C2=A0 =C2=A0 =3D stg.mig_modified_dt=C2=A0 =C2=A0 =C2=A0 prod.= mig_modified_dt=C2=A0 =C2=A0 =3D current_timestamp=C2=A0 =C2=A0WHEN NOT MAT= CHED THEN INSERT=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Service_Center,=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Receipt_Numbe= r,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_= INS_Attny_ID,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Rep_State_Num,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Rep_VOLAG,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0Rep_Rep_Code,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_Last_Name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_First_Name,=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_Middle_Name,=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_Firm_Name,= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_St= reet,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0R= ep_Street_2,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Rep_City,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Rep_State,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0Rep_ZIP,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Rep_Province,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Rep_Postal_code,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rep_Country,=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mig_filename,=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mig_insert_dt,=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mig_modified_dt=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0) SELECT (=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0stg.Service_Center,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Receipt_Num= ber,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_INS_Attny_ID,=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0stg.Rep_State_Num,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg= .Rep_VOLAG,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Rep_Code,=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0stg.Rep_Last_Name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0st= g.Rep_First_Name,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Middle_Name,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Firm_Name,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0stg.Rep_Street,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Street_2= ,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_City,=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0stg.Rep_State,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_ZIP,=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Province,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0stg.Rep_Postal_code,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.Rep_Country,= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stg.mig_filename,=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0current_timestamp,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--stg.mig_ins= ert_dt,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0null=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0--stg.mig_modified_dt=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0)=C2=A0 =C2=A0= ; =C2=A0 =C2=A0----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Set the return code to 0=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Ret :=3D SQLCODE= ;------ Exception error handler----exception=C2=A0 =C2=A0when others then= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_ErrorCode :=3D SQLCODE;=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_ErrorMsg=C2=A0 :=3D SQLERRM;=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_Ret=C2=A0 =C2=A0 =C2=A0 =C2=A0:=3D v_Error= Code; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Commit the record into the Err= orLog=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PERFORM pr_write_error_log( s= ys_context('userenv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s= ys_context('userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 v_ErrorCode, v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Intentionally leaving the "com= mit" to application=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = ----end;$$ LANGUAGE plpgsql; --ERROR:=C2=A0 syntax error at or near "prod"LINE 15: INSERT into cidrdba.s= c_g28 prod=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^SQL state: = 42601Character: 452=C2=A0 even with and/or without alias of cidrdba.gc_g28 table If I do: INSERT into cidrdba.sc_g28 (prod.service_Center,prod.receipt_Numbe= r,prod.rep_INS_Attny_ID,prod.rep_State_Num,prod.rep_VOLAG,=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 prod.Rep_Rep_Code, prod.Rep_L= ast_Name,prod.Rep_First_Name,prod.Rep_Middle_Name, prod.rep_Firm_Name,=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 prod.rep_Street= ,prod.Rep_Street_2, prod.Rep_City,prod.rep_State,prod.rep_ZIP,prod.rep_Prov= ince, prod.Rep_Postal_code,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 prod.Rep_Country, prod.mig_filename, prod.mig_modified_dt)= =C2=A0=C2=A0 =C2=A0 =C2=A0( =C2=A0 =C2=A0SELECT stg.Service_Center, stg.Rec= eipt_Number, stg.Rep_INS_Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.Rep_Rep_Code, s= tg.Rep_Last_Name, stg.Rep_First_Name, stg.Rep_Middle_Name, stg.Rep_Firm_Nam= e,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.Rep_St= reet, stg.Rep_Street_2, stg.Rep_City, stg.Rep_State, stg.rep_ZIP, stg.Rep_P= rovince, stg.Rep_Postal_code,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 stg.ep_Country, stg.mig_filename, stg.mig_modified_dt=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM cidr_staging.STG_G28 stg=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ORDER by stg.mig_seq--=C2=A0 =C2=A0 ) stg= =C2=A0 =C2=A0 =C2=A0 ON=C2=A0 CONFLICT ( prod.receipt_number =3D stg.receip= t_number )=C2=A0=C2=A0 =C2=A0WHEN MATCHED THEN UPDATE SET=C2=A0 =C2=A0 =C2= =A0 prod.Service_Center=C2=A0 =C2=A0 =C2=A0=3D stg.Service_Center,--=C2=A0 = =C2=A0 =C2=A0 prod.Receipt_Number=C2=A0 =C2=A0 =C2=A0=3D stg.Receipt_Number= ,=C2=A0 =C2=A0 =C2=A0 prod.Rep_INS_Attny_ID=C2=A0 =C2=A0=3D stg.Rep_INS_Att= ny_ID,=C2=A0 =C2=A0 =C2=A0 prod.Rep_State_Num=C2=A0 =C2=A0 =C2=A0 =3D stg. = Rep_State_Num,=C2=A0 =C2=A0 =C2=A0 prod.Rep_VOLAG=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =3D stg.Rep_VOLAG, ..... blah ...blah I get:=C2=A0ERROR:=C2=A0 syntax error at or near "ON"LINE 13:=C2=A0 =C2=A0 = =C2=A0 =C2=A0ON=C2=A0 CONFLICT ( prod.receipt_number =3D stg.receipt_numbe.= ..=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^SQL state: 42601C= haracter: 985 v/r, Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love and forgive more.To call= him a dog hardly seems to do him justice though in as much as he had four = legs, a tail, and barked, I admit he was, to all outward appearances. But t= o those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success=20 On Thursday, November 8, 2018 4:16 AM, Guillaume Lelarge wrote: =20 Hi, Le=C2=A0mer. 7 nov. 2018 =C3=A0=C2=A023:52, Pepe TD Vo a= =C3=A9crit=C2=A0: thank you for replying, if there's no Merge stating in Postgres, what state= ment in Postgres should I use?=C2=A0 Update? INSERT ... ON CONFLICT ... (see https://www.postgresql.org/docs/11/sql-inse= rt.html for details) =20 I found merge examples on=C2=A0https://wiki.postgresql.org/wiki/MergeTestE= xamples=C2=A0and the syntax is about the same as Oracle but a function is n= ot working.=C2=A0 Yeah, but at the top of this page, there is this text: "This was never inte= grated into PostgreSQL, and requires significant work to be production qual= ity". =20 If I used update=C2=A0syntax it would long implement for this query.=C2=A0= I'm not a developer and I'm new to Postgres.=C2=A0 Is there a good documen= t you suggest me to use? The manual would be a good place to start: https://www.postgresql.org/docs/ =20 thank you again.=C2=A0Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir mistakes you will be alone. So judge less, love and forgive more.To call= him a dog hardly seems to do him justice though in as much as he had four = legs, a tail, and barked, I admit he was, to all outward appearances. But t= o those who knew him well, he was a perfect gentleman (Hermione Gingold) **Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely = rich *** Faithful talent *** Sharing success=20 On Wednesday, November 7, 2018 5:36 PM, Tom Lane wr= ote: =20 Ron writes: > On 11/07/2018 09:10 AM, Pepe TD Vo wrote: >> *ERROR: "cidrdba.sc_date_in" is not a known variable* > That doesn't look like a "can't merge table from another schema" error. >> *LINE 13:=C2=A0 =C2=A0 MERGE INTO cidrdba.sc_date_in prod* >> *=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^* >> *SQL state: 42601* >> *Character: 352* > What happens when you run the statement through psql? It will fail, of course, since there's no MERGE statement in Postgres. I think the reason for the weird error is that the plpgsql scanner is seeing the "INTO cidrdba.sc_date_in" part and trying to process that as an "INTO plpgsql-variable" clause, before it's fed the rest of the statement to the core parser, which is what would notice that MERGE isn't a known command.=C2=A0 Maybe we could improve matters by reordering that processing, but it might be a lot of work for a small benefit. =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane =20 --=20 Guillaume. =20 ------=_Part_961940_2092650866.1542037716164 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
= I tried INSERT ... ON CONFLICT and still not work

CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_G= 28" (v_Ret OUT int) RETURNS integer
as $$
declare 
        v_ErrorCode&nbs= p;            int;
        v_Er= rorMsg              varchar(512);
<= div dir=3D"ltr" id=3D"yui_3_16_0_ym19_1_1542036787741_10461">    =     v_Module              &nbs= p; varchar(32) =3D 'PR_MIG_STG_G28';

begi= n

   --------------------------= --------
   --### MERGING: STG_G28 into SC_G28
   -------------------= ---------------
--   MERGE INTO cidrdba.sc_g28 prod
--   USING (
<= div dir=3D"ltr" id=3D"yui_3_16_0_ym19_1_1542036787741_10472">INSERT into ci= drdba.sc_g28 prod
USING cidr_staging.STG_G28 stg
ON ( prod.receipt_number =3D stg.receipt_n= umber )
=    WHEN MATCHED THEN UPDATE SET
      prod.Service_Center&= nbsp;    =3D stg.Service_Center,
--      prod.Receipt_Numb= er     =3D stg.Receipt_Number,
      prod.Rep_INS_Att= ny_ID   =3D stg.Rep_INS_Attny_ID,
      prod.Rep_State_Num=       =3D stg. Rep_State_Num,
      prod.Rep_VOLAG&nb= sp;         =3D stg.Rep_VOLAG,
      prod.Rep_Re= p_Code       =3D stg.Rep_Rep_Code,
      prod.Re= p_Last_Name      =3D stg.Rep_Last_Name,
      prod.Re= p_First_Name     =3D stg.Rep_First_Name,
      prod.R= ep_Middle_Name    =3D stg.Rep_Middle_Name,
      prod.Rep_= Firm_Name      =3D stg.Rep_Firm_Name,
      prod.Rep_= Street         =3D stg.Rep_Street,
      = prod.Rep_Street_2       =3D stg.Rep_Street_2,
    &nb= sp; prod.Rep_City           =3D stg.Rep_City,=
  =     prod.Rep_State          =3D stg.Rep_= State,
&= nbsp;     prod.Rep_ZIP            = =3D stg.Rep_ZIP,
      prod.Rep_Province       = =3D stg.Rep_Province,
      prod.Rep_Postal_code    =3D st= g.Rep_Postal_code,
      prod.Rep_Country       = =3D stg.Rep_Country,
      prod.mig_filename      &n= bsp;=3D stg.mig_filename,
      --prod.mig_insert_dt    &n= bsp; =3D stg.mig_insert_dt,
      --prod.mig_modified_dt   = ; =3D stg.mig_modified_dt
      prod.mig_modified_dt    = =3D current_timestamp
   WHEN NOT MATCHED THEN INSERT
       =    (
                  &nbs= p;Service_Center,
                  &n= bsp;Receipt_Number,
                  =  Rep_INS_Attny_ID,
                &nb= sp;  Rep_State_Num,
                &n= bsp;  Rep_VOLAG,
                 = ;  Rep_Rep_Code,
                 = ;  Rep_Last_Name,
                &nbs= p;  Rep_First_Name,
                &n= bsp;  Rep_Middle_Name,
               =    Rep_Firm_Name,
               = ;    Rep_Street,
                =    Rep_Street_2,
                =    Rep_City,
                &nbs= p;  Rep_State,
                  =  Rep_ZIP,
                   = ;Rep_Province,
                   = ;Rep_Postal_code,
                  &n= bsp;Rep_Country,
                  &nb= sp;mig_filename,
                  &nb= sp;mig_insert_dt,
                  &n= bsp;mig_modified_dt
           ) SELECT (
     = ;    stg.Service_Center,
         stg.Receipt_Nu= mber,
&n= bsp;        stg.Rep_INS_Attny_ID,
        &= nbsp;stg.Rep_State_Num,
         stg.Rep_VOLAG,
    &nbs= p;    stg.Rep_Rep_Code,
         stg.Rep_Last_Na= me,
&nbs= p;        stg.Rep_First_Name,
        &nbs= p;stg.Rep_Middle_Name,
         stg.Rep_Firm_Name,
    &= nbsp;    stg.Rep_Street,
         stg.Rep_Street= _2,
&nbs= p;        stg.Rep_City,
         stg.R= ep_State,
         stg.Rep_ZIP,
        &nbs= p;stg.Rep_Province,
         stg.Rep_Postal_code,
    &n= bsp;    stg.Rep_Country,
         stg.mig_filena= me,
&nbs= p;        current_timestamp,
        &nbs= p;--stg.mig_insert_dt,
         null
        &= nbsp;--stg.mig_modified_dt
         )
   ;

   ----
        -- Set the retur= n code to 0
        ----
        v_Ret :=3D SQLCODE= ;
----
-- Except= ion error handler
----
exception
   when others then
           v_E= rrorCode :=3D SQLCODE;
           v_ErrorMsg  := =3D SQLERRM;
           v_Ret      &nb= sp;:=3D v_ErrorCode;

     =           ----
            =     -- Commit the record into the ErrorLog
        &n= bsp;       ----
              &nb= sp; PERFORM pr_write_error_log( sys_context('userenv','session_user'),
   = ;                     &nb= sp;       sys_context('userenv','host'), v_Module,
    &nb= sp;                     &= nbsp;     v_ErrorCode, v_ErrorMsg );

                ----
    &n= bsp;           -- Intentionally leaving the "commi= t" to application
                ----
end;
$$ LANGUAGE plp= gsql;

--
ERROR:  syntax error at or near "prod"=
LINE 15: INSERT into ci= drdba.sc_g28 prod
  &= nbsp;                    =             ^
SQL state: 42601
Character: 452 

even with and/or without alias of cidrdba.gc_g28= table
<= br>
If I= do:
=09INSERT into cidrdba.sc_g28 (prod.service_Center,prod.receipt_Number,prod.= rep_INS_Attny_ID,prod.rep_State_Num,prod.rep_VOLAG,
        &nb= sp;         prod.Rep_Rep_Code, prod.Rep_Last_Name,prod.= Rep_First_Name,prod.Rep_Middle_Name, prod.rep_Firm_Name,
       = ;           prod.rep_Street,prod.Rep_Street_2, pro= d.Rep_City,prod.rep_State,prod.rep_ZIP,prod.rep_Province, prod.Rep_Postal_c= ode,
&nb= sp;                 prod.Rep_Countr= y, prod.mig_filename, prod.mig_modified_dt) 
     (
=09=09   SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_= Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,
          &nbs= p;       stg.Rep_Rep_Code, stg.Rep_Last_Name, stg.Rep_First_= Name, stg.Rep_Middle_Name, stg.Rep_Firm_Name,
          &n= bsp;       stg.Rep_Street, stg.Rep_Street_2, stg.Rep_City, s= tg.Rep_State, stg.rep_ZIP, stg.Rep_Province, stg.Rep_Postal_code,
    &nb= sp;             stg.ep_Country, stg.mig_filen= ame, stg.mig_modified_dt
           FROM cidr_staging= .STG_G28 stg
           ORDER by stg.mig_seq
--    = ) stg
&n= bsp;     ON  CONFLICT ( prod.receipt_number =3D stg.receipt_= number ) 
   WHEN MATCHED THEN UPDATE SET
      prod.Servic= e_Center     =3D stg.Service_Center,
--      prod.Rec= eipt_Number     =3D stg.Receipt_Number,
      prod.Re= p_INS_Attny_ID   =3D stg.Rep_INS_Attny_ID,
      prod.Rep_= State_Num      =3D stg. Rep_State_Num,
      prod.Rep= _VOLAG          =3D stg.Rep_VOLAG, ..... blah ...b= lah

=
I get:&= nbsp;
ERROR:  syntax error at= or near "ON"
= LINE 13:       ON  CONFLICT ( prod.receipt_number = =3D stg.receipt_numbe...
               ^<= /div>
SQL state: 4260= 1
Character: 985

v/r,


<= div id=3D"yui_3_13_0_rc_1_1_1381789421038_7868" class=3D"yiv3811552299ms__i= d13889">
<= span id=3D"yui_3_13_0_rc_1_1_1381789421038_7871" class=3D"yiv3811552299sg">= Bach-Nga

No one in this world is pure and perfect.  If you avoi= d people for their mistakes you will be alone. So judge less, love and forg= ive more.
To call him a dog hardly seems to do h= im justice though in as much as he had four legs, a tail, and barked, I adm= it he was, to all outward appearances. But to those who knew him well, he w= as a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak = kindly.
*** Genuinely rich *** Faithful talent *** Sharing = success


On Thursday, November 8, 2018 4:16 AM, Guillaume Lelarge <guillau= me@lelarge.info> wrote:


=
Hi,

Le mer. 7 nov. 2018 =C3= =A0 23:52, Pepe TD Vo <pepevo@yahoo.com> a =C3=A9crit :
<= blockquote class=3D"yiv4200134598gmail_quote" style=3D"margin:0px 0px 0px 0= .8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">
thank you for replying, if there's no Merge stating in Postgre= s, what statement in Postgres should I use?  Update?

INSERT ... ON CONFLI= CT ... (see https://www.postgresql.or= g/docs/11/sql-insert.html for details)
I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples&= nbsp;and the syntax is about the same as Oracle but a function is not worki= ng. 

= Yeah, but at the top of this page, there is this text: "This was never i= ntegrated into PostgreSQL, and requires significant work to be production q= uality".
If I used update&nb= sp;syntax it would long implement for this query.  I'm not a developer= and I'm new to Postgres.  Is there a good document you suggest me to = use?


The manual would be a good place to sta= rt: https://www.postgresql.org/docs/


thank you again.
 
Bach-Nga

No one in this world is pure and perfect.  If you av= oid people for their mistakes you will be alone. So judge less, love and fo= rgive more.
To call him a dog= hardly seems to do him justice though in as much as he had four legs, a ta= il, and barked, I admit he was, to all outward appearances. But to those wh= o knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.<= /div>
*** Genuinel= y rich *** Faithful talent *** Sharing success


On Wednesday, November 7, 2018 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:


Ron <ronljohnsonjr@gmail.com> writes:

> On 11/07/2018 09:10 AM, Pe= pe TD Vo wrote:
>> *ERROR: "cidrdba.sc_date_in" is = not a known variable*

> That doesn'= t look like a "can't merge table from another schema" error.

>> *LINE 13:    MERGE INTO cidrdba.s= c_date_in prod*
>> *        &nb= sp;      ^*
>> *SQL state: 42601*>> *Character: 352*

> What happens when you run the statement through psql?


It will fail, of course, since there's no ME= RGE statement in Postgres.

I think the= reason for the weird error is that the plpgsql scanner is
seeing the "INTO cidrdba.sc_date_in" part and trying to process that
as an "INTO plpgsql-variable" clause, before it's fed the res= t of the
statement to the core parser, which is what woul= d notice that MERGE
isn't a known command.  Maybe we= could improve matters by reordering
that processing, but= it might be a lot of work for a small benefit.

            regards,= tom lane






--
Guillaume.



= ------=_Part_961940_2092650866.1542037716164--