Re: query can't merge into table of the other schema

[Pepe TD Vo <pepevo@xxxxxxxxx>]

prod is aliase table. 

Is 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=b.column and then insert into table a (select * from table b where a.column is not b.column).  all are worked.

UPDATE "CIDRDBA"."SC_G28" prod SET (

"SERVICE_CENTER", "RECEIPT_NUMBER", "REP_INS_ATTNY_ID", "REP_STATE_NUM", "REP_VOLAG", "REP_REP_CODE", "REP_LAST_NAME", 

"REP_FIRST_NAME", "REP_MIDDLE_NAME", "REP_FIRM_NAME", "REP_STREET", "REP_STREET_2", "REP_CITY", "REP_STATE", "REP_ZIP", 

"REP_PROVINCE", "REP_POSTAL_CODE", "REP_COUNTRY", "MIG_FILENAME", "MIG_MODIFIED_DT")

 =  (

   SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,

                  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, 

                  stg.Rep_Postal_code, stg.ep_Country, stg.mig_filename, stg.mig_modified_dt

           FROM cidr_staging.STG_G28 stg

       WHERE prod.receipt_number = stg.receipt.number

          ORDER by stg.mig_seq

    )

   ;

INSERT INTO "CIDRDBA"."SC_G28" as prod  

 (select * from cidr_staging stg where 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 avoid people for their 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 to 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

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

On Mon, Nov 12, 2018 at 8:49 AM Pepe TD Vo <pepevo@xxxxxxxxx> wrote:
> INSERT into cidrdba.sc_g28 prod
> USING cidr_staging.STG_G28 stg
> ON ( prod.receipt_number = stg.receipt_number )

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

> ERROR:  syntax error at or near "prod"
> LINE 15: INSERT into cidrdba.sc_g28 prod

Then below, where 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_Number,prod.rep_INS_Attny_ID,prod.rep_State_Num,prod.rep_VOLAG,
>                  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, prod.Rep_City,prod.rep_State,prod.rep_ZIP,prod.rep_Province, prod.Rep_Postal_code,
>                  prod.Rep_Country, prod.mig_filename, prod.mig_modified_dt)
>      (
>    SELECT stg.Service_Center, stg.Receipt_Number, stg.Rep_INS_Attny_ID, stg.Rep_State_Num, stg.Rep_VOLAG,
>                  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, stg.Rep_Postal_code,
>                  stg.ep_Country, stg.mig_filename, stg.mig_modified_dt
>            FROM cidr_staging.STG_G28 stg
>            ORDER by stg.mig_seq
> --    ) 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.  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.  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.

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

>                ^

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.