Re: query can't merge into table of the other schema
От | David G. Johnston |
---|---|
Тема | Re: query can't merge into table of the other schema |
Дата | |
Msg-id | CAKFQuwZo5jAa+sPgkto+=Q=kNPnThDvefQ0WPfSkP3fEeVMC_Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: query can't merge into table of the other schema (Pepe TD Vo <pepevo@yahoo.com>) |
Ответы |
Re: query can't merge into table of the other schema
(Pepe TD Vo <pepevo@yahoo.com>)
|
Список | pgsql-admin |
On Mon, Nov 12, 2018 at 8:49 AM Pepe TD Vo <pepevo@yahoo.com> 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.
В списке pgsql-admin по дате отправления:
Предыдущее
От: Rui DeSousaДата:
Сообщение: Re: PostgreSQL 10.5 : Logical replication timeout results in PANIC inpg_wal "No space left on device"