Обсуждение: query can't merge into table of the other schema
Hello,
Would you please tell me why I can't merge table from another schema? I have granted all the privilege from one to another.
this is procedure from Oracle:\
CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (
v_Ret OUT number )
as
v_ErrorCode number;
v_ErrorMsg varchar2(512);
v_Module varchar2(32) := 'PR_MIG_STG_DATE_IN';
begin
----
-- MERGING: STG_DATE_IN into SC_DATE_IN
----
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in, mig_filename,mig_insert_dt,mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Date_In = stg.Date_In,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = sysdate
WHEN NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
----
-- Intentionally leaving the "commit" to application
----
end;
/
I converted to Postgres:
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_DATE_IN" (v_Ret OUT integer ) RETURNS integer
as $$
declare
v_ErrorCode integer;
v_ErrorMsg varchar(512);
v_Module varchar(32) := 'PR_MIG_STG_DATE_IN';
begin
----
-- MERGING: STG_DATE_IN into SC_DATE_IN
----
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in, mig_filename,mig_insert_dt,mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Date_In = stg.Date_In,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = sysdate
WHEN NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- Exception error handler
----
exception when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
-- RAISE NOTICE 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'),
PERFORM pr_write_error_log ( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
end;
$$ LANGUAGE plpgsql;
ERROR: "cidrdba.sc_date_in" is not a known variable
LINE 13: MERGE INTO cidrdba.sc_date_in prod
^
SQL state: 42601
Character: 352
thank you.
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.
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 11/07/2018 09:10 AM, Pepe TD Vo wrote:
[snip]
That doesn't look like a "can't merge table from another schema" error.
What happens when you run the statement through psql?
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in,
mig_filename,mig_insert_dt,
mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Date_In = stg.Date_In,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = sysdate
WHEN NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
[snip]
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.sc_date_in prod^SQL state: 42601Character: 352
What happens when you run the statement through psql?
MERGE INTO cidrdba.sc_date_in prod
USING (
SELECT Receipt_Number,date_in,
mig_filename,mig_insert_dt,
mig_modified_dt
FROM cidr_staging.STG_Date_In
ORDER by mig_seq
) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
-- prod.Receipt_Number = stg.Receipt_Number,
prod.Date_In = stg.Date_In,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = sysdate
WHEN NOT MATCHED THEN INSERT
(
prod.Receipt_Number,
prod.Date_In,
prod.mig_filename,
prod.mig_insert_dt,
prod.mig_modified_dt
) VALUES (
stg.Receipt_Number,
stg.Date_In,
stg.mig_filename,
sysdate,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> 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: MERGE INTO cidrdba.sc_date_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 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. Maybe we could improve matters by reordering that processing, but it might be a lot of work for a small benefit. regards, tom lane
thank you for replying, if there's no Merge stating in Postgres, what statement in Postgres should I use? Update? I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples and the syntax is about the same as Oracle but a function is not working. If I used update 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?
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.
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 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 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.sc_date_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 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. Maybe we could improve matters by reordering
that processing, but it might be a lot of work for a small benefit.
regards, tom lane
> 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: MERGE INTO cidrdba.sc_date_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 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. Maybe we could improve matters by reordering
that processing, but it might be a lot of work for a small benefit.
regards, tom lane
Hi,
--
Le mer. 7 nov. 2018 à 23:52, Pepe TD Vo <pepevo@yahoo.com> a écrit :
thank you for replying, if there's no Merge stating in Postgres, what statement in Postgres should I use? Update?
INSERT ... ON CONFLICT ... (see https://www.postgresql.org/docs/11/sql-insert.html for details)
I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples and the syntax is about the same as Oracle but a function is not working.
Yeah, but at the top of this page, there is this text: "This was never integrated into PostgreSQL, and requires significant work to be production quality".
If I used update 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 start: https://www.postgresql.org/docs/
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.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 successOn 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 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.sc_date_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 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. 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.
I tried INSERT ... ON CONFLICT and still not work
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_G28" (v_Ret OUT int) RETURNS integer
as $$
declare
v_ErrorCode int;
v_ErrorMsg varchar(512);
v_Module varchar(32) = 'PR_MIG_STG_G28';
begin
----------------------------------
--### MERGING: STG_G28 into SC_G28
----------------------------------
-- MERGE INTO cidrdba.sc_g28 prod
-- USING (
INSERT into cidrdba.sc_g28 prod
USING cidr_staging.STG_G28 stg
ON ( 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,
prod.Rep_Rep_Code = stg.Rep_Rep_Code,
prod.Rep_Last_Name = stg.Rep_Last_Name,
prod.Rep_First_Name = stg.Rep_First_Name,
prod.Rep_Middle_Name = stg.Rep_Middle_Name,
prod.Rep_Firm_Name = stg.Rep_Firm_Name,
prod.Rep_Street = stg.Rep_Street,
prod.Rep_Street_2 = stg.Rep_Street_2,
prod.Rep_City = stg.Rep_City,
prod.Rep_State = stg.Rep_State,
prod.Rep_ZIP = stg.Rep_ZIP,
prod.Rep_Province = stg.Rep_Province,
prod.Rep_Postal_code = stg.Rep_Postal_code,
prod.Rep_Country = stg.Rep_Country,
prod.mig_filename = stg.mig_filename,
--prod.mig_insert_dt = stg.mig_insert_dt,
--prod.mig_modified_dt = stg.mig_modified_dt
prod.mig_modified_dt = current_timestamp
WHEN NOT MATCHED THEN INSERT
(
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_insert_dt,
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.Rep_Country,
stg.mig_filename,
current_timestamp,
--stg.mig_insert_dt,
null
--stg.mig_modified_dt
)
;
----
-- Set the return code to 0
----
v_Ret := SQLCODE;
----
-- Exception error handler
----
exception
when others then
v_ErrorCode := SQLCODE;
v_ErrorMsg := SQLERRM;
v_Ret := v_ErrorCode;
----
-- Commit the record into the ErrorLog
----
PERFORM pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
v_ErrorCode, v_ErrorMsg );
----
-- Intentionally leaving the "commit" to application
----
end;
$$ LANGUAGE plpgsql;
--
ERROR: syntax error at or near "prod"
LINE 15: INSERT into cidrdba.sc_g28 prod
^
SQL state: 42601
Character: 452
even with and/or without alias of cidrdba.gc_g28 table
If I do:
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
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...
^
SQL state: 42601
Character: 985
v/r,
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.
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 Thursday, November 8, 2018 4:16 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
Hi,
--
Le mer. 7 nov. 2018 à 23:52, Pepe TD Vo <pepevo@yahoo.com> a écrit :
thank you for replying, if there's no Merge stating in Postgres, what statement in Postgres should I use? Update?
INSERT ... ON CONFLICT ... (see https://www.postgresql.org/docs/11/sql-insert.html for details)
I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples and the syntax is about the same as Oracle but a function is not working.
Yeah, but at the top of this page, there is this text: "This was never integrated into PostgreSQL, and requires significant work to be production quality".
If I used update 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 start: https://www.postgresql.org/docs/
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.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 successRon <ronljohnsonjr@gmail.com> 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: MERGE INTO cidrdba.sc_date_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 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. 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.
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.
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.
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@gmail.com> wrote:
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.
> 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.