Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKWgK-0003nL-E9 for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 22:52:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKWgI-0003ch-CZ for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 22:52:14 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKWgH-0003ca-VW for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 22:52:14 +0000 Received: from sonic306-4.consmr.mail.bf2.yahoo.com ([74.6.132.43]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKWgA-0002r5-UX for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 22:52:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541631124; bh=fH8ipYitKLKzu+1Kl6gWU3QvSwZe9sCPP8aEZ37i9No=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=doI3+SZYw4rYpLrmUJbxcv/jjB/NbZ4odg4LEM7XzkmdOBBCE1+fnZrQ7lF1SSCNNj0kvzTvcV/898eIKDyZQETwfR0i330Btf7a+LBO+/McdzQM1u8R2RDsCy6hpsUd7hm3/R5wexcMYyIM5iBaMMNNuqdFDKbf9cPxGMG45Dg+0HKjwD02gZIq9pSlrmGLzDjQ5CCoprS+nnIX1UJwYuK1gSSv6Y/wdaoJl4H+kLsEAERysmTKKGj7hniutAajUDjx+aZhnnVkK+rAiT1yPuUCwAqM1YWfd+PADcGtjG8lpFRxy8wg/Y9XylwAAOfLuZD/h6a1oJL6nLkemoDJFw== X-YMail-OSG: Yu9T4agVM1kDUf5QOc25ltLtlP8bn8Qpyjj54VlVQhk_FNZWumq_UN_yA2PaoTx xHIRO87PbLp1hU9nhMiKcRqUwCCsXK.bN_n1B11n8_.sfIgMOLCTerCpVXb26DRdNENp9FnT7IBN 2KDhngoaqWH5pB85orbhmfJ70nXyIYN7wDesGSjx0cDjexm5T419yPcnx06.M_Y6mJBI21nMZmL_ P7Ylo_mifKr2PYzhIFLzIUkKdhbZ_U3lwkvAngGBZPlJxKyLCG_ZhXrLxGP1DHc0lXKYbyedq1j. .OQTyYgdhRLQ5BUN1yFq26aX5seDtCN2O4tbBfl3Xn6NfbdsZcHCWnVuziTlQ3CPk.jsHGSlph6H Yu8MhsN8pi6J_cHmAsG1xnCMPNJq22H0g2.2Zym3qdBJ5ESvXiiCXlqp0r07ecdOPgPchd69wnEm S9ekdS1bIIPtvfphqLQ2YgGzVrlY.1NFq6BLfXhcb7eKK_yugADgBjTWRSagyAtvgWZQSd5AlOcJ lT54gAglF15rmGnpp5r8fBiWpQwtqrotecN4FGQMiLqkZXb2T7z6YdBpyrEr7orMXoJhzAIEzddU yNhHASCvLH1yJ6MyZ5tZhxrNROHJ1W4v7dZmaT9igNKVVmmprKligb0.00CcTuTpFwy_bjh39E1N blbvkoH0U7HKGRBgqEcd3EJAFCrSTp4zT_i9cqNh0HJL2wn6H4ROabHuT8t6bRK0wPNu6LcSk8pO 4INl4gc56csvvGphVksdmUMQX3olR5MprDoswlJbS_qBl3GK6hwjDIItbCdy3MNWgKEMNBtgX4Ra CXVpfN_2k2F.zOF9iwj6cBaEc.C44CDtC32NEOnxwQiTlGH3BaE.VjN9SJMfe1qbvCkK_YtDR3_F bKXQdJhdAfT9nvTH2_amAu8G5o.2nKETDe4qezAg5uA6ci33YyhTND9GN7U2rgP6f5I3vCycoUWH 9WvGF31RYNxJL1c02p5MaSUf1wkcs967OIoMhahtf8nsHPOnokEmQaOyv2fIbAqUuUQMYZdN01g7 .QicBWI2B3hFE9Q_F Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.bf2.yahoo.com with HTTP; Wed, 7 Nov 2018 22:52:04 +0000 Date: Wed, 7 Nov 2018 22:52:01 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Tom Lane , Ron Cc: "pgsql-admin@lists.postgresql.org" Message-ID: <832768265.636177.1541631121047@mail.yahoo.com> In-Reply-To: <12036.1541630162@sss.pgh.pa.us> References: <1425722754.312953.1541603425493.ref@mail.yahoo.com> <1425722754.312953.1541603425493@mail.yahoo.com> <12036.1541630162@sss.pgh.pa.us> Subject: Re: query can't merge into table of the other schema MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_636176_1980428367.1541631121045" X-Mailer: WebService/1.1.12710 YahooMailNeo Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.77 Safari/537.36 Content-Length: 7820 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_636176_1980428367.1541631121045 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable thank you for replying, if there's no Merge stating in Postgres, what state= ment in Postgres should I use?=C2=A0 Update? I found merge examples on=C2= =A0https://wiki.postgresql.org/wiki/MergeTestExamples=C2=A0and the syntax i= s about the same as Oracle but a function is not working.=C2=A0 If I used u= pdate=C2=A0syntax it would long implement for this query.=C2=A0 I'm not a d= eveloper and I'm new to Postgres.=C2=A0 Is there a good document you sugges= t me to use? 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 ------=_Part_636176_1980428367.1541631121045 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
thank you for replying, if there's no Merge stating in= Postgres, what statement in Postgres should I use?  Update? I found m= erge examples on https://wiki.postgresql.org/w= iki/MergeTestExamples and the syntax is about the same as Oracle b= ut a function is not working.  If I used update syntax it would l= ong implement for this query.  I'm not a developer and I'm new to Post= gres.  Is there a good document you suggest me to use?

thank you again.
 
=
Bach-Nga

No one in this world= is pure and perfect.  If you avoid people for their mistakes you will= be alone. So judge less, love and forgive more.
=
T= o 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 to those who knew him well, he was a perfect gentleman (Hermione Gingo= ld)

**Live simply **L= ove generously **Care deeply **Speak kindly.
*** Genuinely = 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, Pepe T= D Vo wrote:
>> *ERROR: "cidrdba.sc_date_in" is not = a known variable*

> That doesn't lo= ok like a "can't merge table from another schema" error.
=
>> *LINE 13:    MERGE INTO cidrdba.sc_da= te_in prod*
>> *          =      ^*
>> *SQL state: 42601*
>> *Character: 352*

= > What happens when you run the statement through psql?


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

I think the r= eason 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.  Maybe we c= ould improve matters by reordering
that processing, but i= t might be a lot of work for a small benefit.

            regards,= tom lane

=


<= /body> ------=_Part_636176_1980428367.1541631121045--