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 1gMHA5-00018J-Mi for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 18:42:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gMHA3-0005Jo-A0 for pgsql-admin@arkaria.postgresql.org; Mon, 12 Nov 2018 18:42:11 +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 1gMHA2-0005BJ-MX for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 18:42:11 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gMHA0-0004Cj-1S for pgsql-admin@lists.postgresql.org; Mon, 12 Nov 2018 18:42:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1542048125; bh=Ck8X89DuszUXqtKA5oqp149NtF52l2dNGmeZQ5IbofY=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=EyzgVKZmn5b93KGuNkzJJm2sz0taoLcFKxr4G0IjR6VsorgE8F5dxX5wCJ7BaA5+jviKOqECq4esjdQI10MNou0oP31ytcbhwpk5ztSDGQw+LiQW6FQMkQTfvp2WgR0iBpernrqxqgM1g6T9Z3X7maI5sXZBFx4V1tS6AQRD5T8im5GoNcZV43Wx4Jb9DMuln7QH+qUedRMxh0k4nUtaqXJZxFATaRJOdoJQPxVM5jjjhfqy0YA7iURfU6Ahz97YPQhwand1RfI0y9s/9TLzQsH7jiV/P+u7svE9ePg+zKcvJl8xvZ4BGOly4NAxRp6TvE6fvcqTwEyTFmZPFWQICg== X-YMail-OSG: 3O3eadwVM1lRsMWul_kwca.09NRwR_wALAD05a8jEcz7ZNW1MOsc1.fPcFKdWuB 5ZQevYVeOFRGSuyPcMwq1MUiCzaSRc6FGjW5QzbWie4GatgJsHGMYNyyiF3_pBURpEefUVh6JNXE TlFA64uhj6OvZ7NeIfYBaNw8pnnK1tgNs6RL85VvKYxdZXCvi8hZVBEUbLBitwowv11YgGAqAqZ7 g2h0oR0ANrGgdhpzk9KZdQeZU692ciHK5KK1GlfS3apkAgw3kkuPXLx4EsNV85OCNSNZJRMCG9xE Q_rZs6tUS1.sJOu_cYVGWnLOPopBruJXAGq6GklUBc0PQ0uCiNJBpJyvMCF.3Wo.KciAOHcEK_pq si5N7IZm.8eK3JZFP24EGtNa.UuZVF7jmWFk33XKYHqgeelrIAxEKPt13gPqzawjdQDmWPeyf0Jm v2PaWkfzvvEkFQv727YLHuwayDiudCoXXgWYb.75EJi2Ct3gl_tslmcJgtBjnRVDoskHu2kUE8Dz hu7oIpUXy_S6vCqh1GTwem_7VhDJWAcoEuRG.qv43OMI4b8Z.dW.NirGNDaJd2qKb_YBe0sNYl4S qEtBq9VhiDRrgQD4p2TXcgAr.6aQvPLAZ.WlM91Nb5y_gdT3rVVt_zzVBbTSnT7tG8x_xI7zAjOe 51a42gn2Ql_8T8FUPq2UTBcEBn4UutUWvvAz1O_52P_6.kPmCDAQYt7ghTj77gcfOOfR7IrhEOAE Bj3NDfVDxIYi1EwT1uELafowffH9EbQkxmZd4WXUWxhqdyvVMBA4ghgbjfMKjqKapF9vyH8a3nDr 2k_yI.6i0J_SqdGtsIinej6tn.egoNm1wWRBArIUJBoz9HQmkq7s5etgq6ZaaqE.j8n18Dyic5fS alAvWB8MaEyWpdY_mtRWoPKHuY_rmMmlEcfsPbtA7qt3CVxizuBMabB3jfmOiZF4Y6CUh1fe1ssA GiO3UsJB4F7Xb07jpBMF0Bdd2vTjZ85ygNK50o5tGv_bp4wIogaigxScyK6Lz.r_2ZdOayuM- Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Mon, 12 Nov 2018 18:42:05 +0000 Date: Mon, 12 Nov 2018 18:42:00 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "David G. Johnston" Cc: Guillaume Lelarge , Tom Lane , Ron , "pgsql-admin@lists.postgresql.org" Message-ID: <1239821213.1078612.1542048120534@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> <1122299377.961941.1542037716174@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_1078611_613217879.1542048120530" 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: 19500 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1078611_613217879.1542048120530 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable prod is aliase table.=C2=A0Is that mean I can't populate update table from = another table using insert ... conflict? If I do simple insert table a from table b if a.column=3Db.column and then = insert into table a (select * from table b where a.column is not b.column).= =C2=A0 all are worked. UPDATE "CIDRDBA"."SC_G28" prod SET ( "SERVICE_CENTER", "RECEIPT_NUMBER", "R= EP_INS_ATTNY_ID", "REP_STATE_NUM", "REP_VOLAG", "REP_REP_CODE", "REP_LAST_N= AME",=C2=A0 "REP_FIRST_NAME", "REP_MIDDLE_NAME", "REP_FIRM_NAME", "REP_STRE= ET", "REP_STREET_2", "REP_CITY", "REP_STATE", "REP_ZIP",=C2=A0 "REP_PROVINC= E", "REP_POSTAL_CODE", "REP_COUNTRY", "MIG_FILENAME", "MIG_MODIFIED_DT")=C2= =A0=3D=C2=A0 ( =C2=A0 =C2=A0SELECT stg.Service_Center, stg.Receipt_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, stg.Rep_Last_N= ame, stg.Rep_First_Name, stg.Rep_Middle_Name, stg.Rep_Firm_Name,=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.Rep_Street, stg.Re= p_Street_2, stg.Rep_City, stg.Rep_State, stg.rep_ZIP, stg.Rep_Province,=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.Rep_P= ostal_code, 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=A0WHERE prod.receipt_number =3D stg.receipt.number=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ORDER by stg.mig_seq=C2=A0 =C2=A0 )=C2=A0 =C2=A0; INSERT INTO "CIDRDBA"."SC_G28" as prod=C2=A0=C2=A0=C2=A0(select * from cidr= _staging stg where prod.receipt_number <> stg.receipt_number)=C2=A0; Query returned successfully in 99 msec. =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 Monday, November 12, 2018 11:37 AM, David G. Johnston wrote: =20 On Mon, Nov 12, 2018 at 8:49 AM Pepe TD Vo wrote: > INSERT into cidrdba.sc_g28 prod > USING cidr_staging.STG_G28 stg > ON ( prod.receipt_number =3D stg.receipt_number ) Per the documentation that isn't valid PostgreSQL syntax for an insert command; thus the following error. > ERROR:=C2=A0 syntax error at or near "prod" > LINE 15: INSERT into cidrdba.sc_g28 prod Then below, where does "prod" come from?=C2=A0 INSERT can only target a single table - you do not need to prefix the column names in the column list for the INSERT itself. > INSERT into cidrdba.sc_g28 (prod.service_Center,prod.receipt_Number,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_Re= p_Code, prod.Rep_Last_Name,prod.Rep_First_Name,prod.Rep_Middle_Name, prod.r= ep_Firm_Name, >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 prod.rep_St= reet,prod.Rep_Street_2, prod.Rep_City,prod.rep_State,prod.rep_ZIP,prod.rep_= Province, prod.Rep_Postal_code, >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 prod.Rep_Co= untry, prod.mig_filename, prod.mig_modified_dt) >=C2=A0 =C2=A0 =C2=A0 ( >=C2=A0 =C2=A0 SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_A= ttny_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, stg.Rep_Last_Name, stg.Rep_First_Name, stg.Rep_Middle_Name, stg.Rep_= Firm_Name, >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.Rep_Str= eet, stg.Rep_Street_2, stg.Rep_City, stg.Rep_State, stg.rep_ZIP, stg.Rep_Pr= ovince, stg.Rep_Postal_code, >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stg.ep_Coun= try, stg.mig_filename, stg.mig_modified_dt >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM cidr_staging.STG_G28 stg >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ORDER by stg.mig_seq > --=C2=A0 =C2=A0 ) stg This shows you commented out the closing parens and alias line wrapping the select statement - that seems wrong, though mostly that you had an opening parens in place (never tried it, not sure it works...). Furthermore; your ON CONFLICT attempt does not following the documentation.=C2=A0 You don't reference tables in the queries - the left side of assignment is known to refer to the INSERT target and the right side uses keywords, namely EXCLUDED (which is poorly only mentioned in the examples - it should be mentioned in the authoritative ON CONFLICT clause body) to reference the final value computed to be inserted.=C2=A0 Likewise only references to INSERT target properties are allowed in ON CONFLICT since at the point of insert the row data being inserted exists independently of any source table and is strictly being compared to other rows on the insert target relative to the constraints defined thereon. >=C2=A0 =C2=A0 =C2=A0 ON=C2=A0 CONFLICT ( prod.receipt_number =3D stg.recei= pt_number ) >=C2=A0 =C2=A0 WHEN MATCHED THEN UPDATE SET >=C2=A0 =C2=A0 =C2=A0 prod.Service_Center=C2=A0 =C2=A0 =3D stg.Service_Cent= er, > --=C2=A0 =C2=A0 =C2=A0 prod.Receipt_Number=C2=A0 =C2=A0 =3D stg.Receipt_N= umber, >=C2=A0 =C2=A0 =C2=A0 prod.Rep_INS_Attny_ID=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 stg. Rep_S= tate_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: > ERROR:=C2=A0 syntax error at or near "ON" > LINE 13:=C2=A0 =C2=A0 =C2=A0 ON=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 ^ You might want to play around with things in the framework on a clean-slate play example writing PostgreSQL code from scratch instead of trying to convert other code to PostgreSQL and ending up with a mix that is non-functional in both. You might think about skipping the whole ON CONFLICT piece and perform separate INSERT and UPDATE commands for records that don't, and do, exist on the target table already respectively. David J. =20 ------=_Part_1078611_613217879.1542048120530 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
prod is aliase table. <= /div>
Is that= mean I can't populate update table from another table using insert ... con= flict?
=
If= I do simple insert table a from table b if a.column=3Db.column and then in= sert into table a (select * from table b where a.column is not b.column).&n= bsp; all are worked.

UPDATE "CIDRDBA"."SC_G28" prod SET (
=09=09"SERVICE_= CENTER", "RECEIPT_NUMBER", "REP_INS_ATTNY_ID", "REP_STATE_NUM", "REP_VOLAG"= , "REP_REP_CODE", "REP_LAST_NAME", 
=09"REP_FIRST_NAME", "RE= P_MIDDLE_NAME", "REP_FIRM_NAME", "REP_STREET", "REP_STREET_2", "REP_CITY", = "REP_STATE", "REP_ZIP", 
=09"REP_PROVINCE", "REP_POSTAL_CODE"= , "REP_COUNTRY", "MIG_FILENAME", "MIG_MODIFIED_DT")
 =3D  (
=09=09   SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_= Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,
          &nb= sp;       stg.Rep_Rep_Code, stg.Rep_Last_Name, stg.Rep_First= _Name, stg.Rep_Middle_Name, stg.Rep_Firm_Name,
          =         stg.Rep_Street, stg.Rep_Street_2, stg.Rep_City,= stg.Rep_State, stg.rep_ZIP, stg.Rep_Province, 
        &= nbsp;         stg.Rep_Postal_code, stg.ep_Country, stg.= mig_filename, stg.mig_modified_dt
           FROM ci= dr_staging.STG_G28 stg
=09       WHERE prod.re= ceipt_number =3D stg.receipt.number
          ORDER by st= g.mig_seq
    )
   ;

INSERT INTO= "CIDRDBA"."SC_G28" as prod  
 (select * from cidr_staging stg wher= e prod.receipt_number <> stg.receipt_number)
 ;

Query returned successfully in 99 msec.
=
 = ;
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 tail, and barked= , I admit he was, to all outward appearances. But to those who knew him wel= l, he was a perfect gentleman (Hermione Gingold)
=
**Live simply **Love generously **Care deeply *= *Speak kindly.
*** Genuinely rich *** Faithful talent *** Sh= aring success

On Monday, November 12, 2018 11:37 AM, David G. Johnston <= ;david.g.johnston@gmail.com> wrote:


On Mon, Nov 12, 2018 at 8:49 AM Pepe= TD Vo <pepevo@yahoo.com> wrote:
&g= t; INSERT into cidrdba.sc_g28 prod
> USING cidr_stagin= g.STG_G28 stg
> ON ( prod.receipt_number =3D stg.recei= pt_number )

Per the documentation that= isn't valid PostgreSQL syntax for an insert
command; thu= s the following error.

> ERROR:&nbs= p; syntax error at or near "prod"
> LINE 15: INSERT in= to cidrdba.sc_g28 prod

Then below, whe= re does "prod" come from?  INSERT can only target a
= single table - you do not need to prefix the column names in the
column list for the INSERT itself.

> INSERT into cidrdba.sc_g28 (prod.service_Center,prod.receipt_Nu= mber,prod.rep_INS_Attny_ID,prod.rep_State_Num,prod.rep_VOLAG,
>                  pr= od.Rep_Rep_Code, prod.Rep_Last_Name,prod.Rep_First_Name,prod.Rep_Middle_Nam= e, prod.rep_Firm_Name,
>        &n= bsp;         prod.rep_Street,prod.Rep_Street_2, prod.R= ep_City,prod.rep_State,prod.rep_ZIP,prod.rep_Province, prod.Rep_Postal_code= ,
>              &n= bsp;   prod.Rep_Country, prod.mig_filename, prod.mig_modified_dt)
>      (
>  &nbs= p; SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_Attny_ID, stg= .Rep_State_Num, stg.Rep_VOLAG,
>      &= nbsp;           stg.Rep_Rep_Code, stg.Rep_Last_Na= me, stg.Rep_First_Name, stg.Rep_Middle_Name, stg.Rep_Firm_Name,
>                  = stg.Rep_Street, stg.Rep_Street_2, stg.Rep_City, stg.Rep_State, stg.rep_ZIP,= stg.Rep_Province, stg.Rep_Postal_code,
>   =               stg.ep_Country, stg.mig_= filename, stg.mig_modified_dt
>      &n= bsp;     FROM cidr_staging.STG_G28 stg
>&nbs= p;           ORDER by stg.mig_seq
> --    ) stg

This show= s you commented out the closing parens and alias line
wra= pping the select statement - that seems wrong, though mostly that
you had an opening parens in place (never tried it, not sure itworks...).

Furthermor= e; your ON CONFLICT attempt does not following the
docume= ntation.  You don't reference tables in the queries - the left
side of assignment is known to refer to the INSERT target and th= e
right side uses keywords, namely EXCLUDED (which is poo= rly only
mentioned in the examples - it should be mention= ed in the
authoritative ON CONFLICT clause body) to refer= ence the final value
computed to be inserted.  Likew= ise only references to INSERT target
properties are allow= ed in ON CONFLICT since at the point of insert the
row da= ta being inserted exists independently of any source table and
is strictly being compared to other rows on the insert target relativ= e
to the constraints defined thereon.


> =     ON  CONFLICT ( prod.receipt_number =3D stg.receipt_num= ber )
>    WHEN MATCHED THEN UPDATE SET
>      prod.Service_Center    = =3D stg.Service_Center,
> --      prod.= Receipt_Number    =3D stg.Receipt_Number,
>=       prod.Rep_INS_Attny_ID  =3D stg.Rep_INS_Attny_ID= ,
>      prod.Rep_State_Num  &nbs= p;   =3D stg. Rep_State_Num,
>     = ; prod.Rep_VOLAG          =3D stg.Rep_VOLAG, ....= . blah ...blah
>
> I get:
> ERROR:  syntax error at or near "ON"
> LINE 13:      ON  CONFLICT ( prod.receipt_number= =3D stg.receipt_numbe...

>      =           ^

Y= ou might want to play around with things in the framework on a
clean-slate play example writing PostgreSQL code from scratch instead=
of trying to convert other code to PostgreSQL and ending= up with a mix
that is non-functional in both.

You might think about skipping the whole ON CO= NFLICT piece and perform
separate INSERT and UPDATE comma= nds for records that don't, and do,
exist on the target t= able already respectively.

David J.



------=_Part_1078611_613217879.1542048120530--