pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMerge statement
13+ messages / 6 participants
[nested] [flat]
* Merge statement
@ 2019-09-20 17:06 Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-20 17:06 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Experts,
I have a script to do merging if the two tables' information are match and if not then do the update. I don't know what I did wrong, would you please help out?
create or replace FUNCTION "ECISDRDM"."PR_MIG_STG_APPL_CDIM" (v_Ret OUT bigint )as $$declare v_ErrorCode bigint; v_ErrorMsg varchar(512); v_Module varchar(32) = 'PR_MIG_STG_APPL_CDIM';
begin
---- -- MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM ---- MERGE INTO "ECISDRDM"."APPLICATION_CDIM" prod USING ( SELECT Receipt_Number,application_id, init_frm_id, frm_typ_id, init_src_sys_id, init_svc_ctr_id, crtd_user_id, sbmtd_dt_id, mig_filename, mig_modified_dt FROM stg_application_cdim ORDER by mig_filename ) stg ON ( prod.receipt_number = stg.receipt_number ) WHEN MATCHED THEN UPDATE SET prod.application_id = stg.application_id, prod.init_frm_id = stg.init_frm_id, prod.frm_typ_id = stg.frm_typ_id, prod.init_src_sys_id = stg.init_src_sys_id, prod.init_svc_ctr_id = stg.init_svc_ctr_id, prod.crtd_user_id = stg.crtd_user_id, prod.sbmtd_dt_id = stg.sbmtd_dt_id, prod.mig_filename = stg.mig_filename, prod.mig_modified_dt = current_timestamp WHEN NOT MATCHED THEN INSERT (prod.Receipt_Number, prod.application_id, prod.init_frm_id, prod.frm_typ_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.crtd_user_id, prod.sbmtd_dt_id, prod.mig_filename ) SELECT ( stg.Receipt_Number, stg.application_id, stg.init_frm_id, stg.frm_typ_id, stg.init_src_sys_id, stg.init_svc_ctr_id, stg.crtd_user_id, stg.sbmtd_dt_id, stg.mig_filename ) ;
---- -- 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;$$ language plpgsql;
ERROR: "application_cdim" is not a known variableLINE 13: MERGE INTO APPLICATION_CDIM prod ^SQL state: 42601Character: 349
even I take schema_name, ECISDRDRM out, I still get an error:
ERROR: "application_cdim" is not a known variableLINE 13: MERGE INTO APPLICATION_CDIM prod ^SQL state: 42601Character: 349
thank you for your help.
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.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
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
@ 2019-09-20 18:21 ` Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Igor Neyman @ 2019-09-20 18:21 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
ERROR: "application_cdim" is not a known variable
LINE 13: MERGE INTO APPLICATION_CDIM prod
^
SQL state: 42601
Character: 349
There is no MERGE in Postgres.
Is this migrated from Oracle?
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
@ 2019-09-20 21:33 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-20 21:33 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>; Igor Neyman <ineyman@perceptron.com>
yes, this is to migrate from Oracle to Postgres. I followed this link and as same as ora2pg
MERGE
|
|
| |
MERGE
|
|
|
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.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 Friday, September 20, 2019, 02:21:49 PM EDT, Igor Neyman <ineyman@perceptron.com> wrote:
#yiv7064530853 #yiv7064530853 -- _filtered #yiv7064530853 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv7064530853 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv7064530853 {font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv7064530853 #yiv7064530853 p.yiv7064530853MsoNormal, #yiv7064530853 li.yiv7064530853MsoNormal, #yiv7064530853 div.yiv7064530853MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New serif;}#yiv7064530853 a:link, #yiv7064530853 span.yiv7064530853MsoHyperlink {color:#0563C1;text-decoration:underline;}#yiv7064530853 a:visited, #yiv7064530853 span.yiv7064530853MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;}#yiv7064530853 p {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:New serif;}#yiv7064530853 span.yiv7064530853ydpa47aad4yiv3811552299sg {}#yiv7064530853 span.yiv7064530853EmailStyle19 {font-family:sans-serif;color:#1F497D;}#yiv7064530853 .yiv7064530853MsoChpDefault {font-size:10.0pt;} _filtered #yiv7064530853 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv7064530853 div.yiv7064530853WordSection1 {}#yiv7064530853
ERROR: "application_cdim" is not a known variable
LINE 13: MERGE INTO APPLICATION_CDIM prod
^
SQL state: 42601
Character: 349
There is no MERGE in Postgres.
Is this migrated from Oracle?
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
@ 2019-09-20 21:40 ` Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Carrie Berlin @ 2019-09-20 21:40 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Igor Neyman <ineyman@perceptron.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
There is no transaction processing with in a Postgres function until you
get to Postgres 11.
You can run a merge in Postgres directly through sql.
On Fri, Sep 20, 2019 at 17:33 Pepe TD Vo <pepevo@yahoo.com> wrote:
> yes, this is to migrate from Oracle to Postgres. I followed this link and
> as same as ora2pg
>
>
> MERGE
> <https://www.postgresql.org/message-id/attachment/23520/sql-merge.html;
>
> MERGE
>
> <https://www.postgresql.org/message-id/attachment/23520/sql-merge.html;
>
>
> 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.
> 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 Friday, September 20, 2019, 02:21:49 PM EDT, Igor Neyman <
> ineyman@perceptron.com> wrote:
>
>
>
>
>
>
> ERROR: "application_cdim" is not a known variable
>
> LINE 13: MERGE INTO APPLICATION_CDIM prod
>
> ^
>
> SQL state: 42601
>
> Character: 349
>
>
>
>
>
> There is no MERGE in Postgres.
>
> Is this migrated from Oracle?
>
>
>
> Regards,
>
> Igor Neyman
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
@ 2019-09-20 22:15 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-20 22:15 UTC (permalink / raw)
To: Carrie Berlin <berlincarrie@gmail.com>; +Cc: Igor Neyman <ineyman@perceptron.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-admin
you said, I can use my script transaction processing within a Postgres function in Postgres 11?
thank you,
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.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 Friday, September 20, 2019, 05:41:21 PM EDT, Carrie Berlin <berlincarrie@gmail.com> wrote:
There is no transaction processing with in a Postgres function until you get to Postgres 11.You can run a merge in Postgres directly through sql.
On Fri, Sep 20, 2019 at 17:33 Pepe TD Vo <pepevo@yahoo.com> wrote:
yes, this is to migrate from Oracle to Postgres. I followed this link and as same as ora2pg
MERGE
|
|
| |
MERGE
|
|
|
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.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 Friday, September 20, 2019, 02:21:49 PM EDT, Igor Neyman <ineyman@perceptron.com> wrote:
ERROR: "application_cdim" is not a known variable
LINE 13: MERGE INTO APPLICATION_CDIM prod
^
SQL state: 42601
Character: 349
There is no MERGE in Postgres.
Is this migrated from Oracle?
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
@ 2019-09-21 05:57 ` Morris de Oryx <morrisdeoryx@gmail.com>
2019-09-21 12:20 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-23 14:34 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 2 replies; 13+ messages in thread
From: Morris de Oryx @ 2019-09-21 05:57 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Postgres has had stored functions for ages, but only added stored
procedures in version 11. Historically, you'll see people calling stored
functions in Postgres "stored procedures" as that's what other folks call
them. It didn't matter because Postgres only had the one thing. For what's
new in Postgres 11 stored procedures, here's a place to look:
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11
The change you're referring to is that a stored procedure (but not a
function) has the ability to manage its own transaction blocks.
I actually have no clue...I've only read about the new stored procedures.
But if you're coming from Oracle, you're likely looking for PG 11 stored
procedures, at least some of the time.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
@ 2019-09-21 12:20 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 12:50 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-21 12:20 UTC (permalink / raw)
To: Morris de Oryx <morrisdeoryx@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thank you all. Will look into it on Monday.
Have a pleasant weekend.
V/r,
Bach Nga
Sent from my iPad
On Sep 21, 2019, at 1:57 AM, Morris de Oryx <morrisdeoryx@gmail.com> wrote:
Postgres has had stored functions for ages, but only added stored procedures in version 11. Historically, you'll see people calling stored functions in Postgres "stored procedures" as that's what other folks call them. It didn't matter because Postgres only had the one thing. For what's new in Postgres 11 stored procedures, here's a place to look:
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11
The change you're referring to is that a stored procedure (but not a function) has the ability to manage its own transaction blocks.
I actually have no clue...I've only read about the new stored procedures. But if you're coming from Oracle, you're likely looking for PG 11 stored procedures, at least some of the time.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
2019-09-21 12:20 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
@ 2019-09-21 12:50 ` Carrie Berlin <berlincarrie@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Carrie Berlin @ 2019-09-21 12:50 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Morris de Oryx <morrisdeoryx@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
I know that it is a procedure and not a function,
On Sat, Sep 21, 2019 at 08:20 Pepe TD Vo <pepevo@yahoo.com> wrote:
> Thank you all. Will look into it on Monday.
>
> Have a pleasant weekend.
>
> V/r,
>
> Bach Nga
>
> Sent from my iPad
>
> On Sep 21, 2019, at 1:57 AM, Morris de Oryx <morrisdeoryx@gmail.com>
> wrote:
>
> Postgres has had stored functions for ages, but only added stored
> procedures in version 11. Historically, you'll see people calling stored
> functions in Postgres "stored procedures" as that's what other folks call
> them. It didn't matter because Postgres only had the one thing. For what's
> new in Postgres 11 stored procedures, here's a place to look:
>
>
> https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11
>
> The change you're referring to is that a stored procedure (but not a
> function) has the ability to manage its own transaction blocks.
>
> I actually have no clue...I've only read about the new stored procedures.
> But if you're coming from Oracle, you're likely looking for PG 11 stored
> procedures, at least some of the time.
>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
@ 2019-09-23 14:34 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-23 14:48 ` Re: Merge statement Keith <keith@keithf4.com>
1 sibling, 1 reply; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-23 14:34 UTC (permalink / raw)
To: Morris de Oryx <morrisdeoryx@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
good morning, happy Monday.
fyi, I upgraded postgres in AWS to 11.1 and the merge function for procedure is still not working. Still complained the same error that: ERROR: "ecisdrdm.application_cdim" is not a known variableLINE 14: MERGE INTO ecisdrdm.application_cdim prod ^SQL state: 42601Character: 348
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.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 Sunday, September 22, 2019, 10:36:01 PM EDT, Morris de Oryx <morrisdeoryx@gmail.com> wrote:
Postgres has had stored functions for ages, but only added stored procedures in version 11. Historically, you'll see people calling stored functions in Postgres "stored procedures" as that's what other folks call them. It didn't matter because Postgres only had the one thing. For what's new in Postgres 11 stored procedures, here's a place to look:
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11
The change you're referring to is that a stored procedure (but not a function) has the ability to manage its own transaction blocks.
I actually have no clue...I've only read about the new stored procedures. But if you're coming from Oracle, you're likely looking for PG 11 stored procedures, at least some of the time.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
2019-09-23 14:34 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
@ 2019-09-23 14:48 ` Keith <keith@keithf4.com>
2019-09-23 15:48 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Keith @ 2019-09-23 14:48 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Morris de Oryx <morrisdeoryx@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Sep 23, 2019 at 10:34 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
> good morning, happy Monday.
>
> fyi, I upgraded postgres in AWS to 11.1 and the merge function for
> procedure is still not working. Still complained the same error that:
> ERROR: "ecisdrdm.application_cdim" is not a known variable
> LINE 14: MERGE INTO ecisdrdm.application_cdim prod
> ^
> SQL state: 42601
> Character: 348
>
>
> v/r,
>
> *Bach-Nga*
>
>
As others have said, MERGE is not a command in Postgres. The document you
linked is a development document from a long time ago when it was proposed
at one time, but it was never accepted. Please reference the actual
PostgreSQL documentation.
https://www.postgresql.org/docs/current/index.html
If you need UPSERT like behavior, look at the INSERT ... ON CONFLICT
statement.
https://www.postgresql.org/docs/current/sql-insert.html
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` RE: Merge statement Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Re: Merge statement Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Re: Merge statement Morris de Oryx <morrisdeoryx@gmail.com>
2019-09-23 14:34 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-23 14:48 ` Re: Merge statement Keith <keith@keithf4.com>
@ 2019-09-23 15:48 ` Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-23 15:48 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Morris de Oryx <morrisdeoryx@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes, I heard MERGE is not a command in Postgres but the others said merge statement added stored procedures in version 11
I upgraded my postgresql from 10.6 to 11.1. in AWS, still not work.
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.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, September 23, 2019, 10:49:34 AM EDT, Keith <keith@keithf4.com> wrote:
On Mon, Sep 23, 2019 at 10:34 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
good morning, happy Monday.
fyi, I upgraded postgres in AWS to 11.1 and the merge function for procedure is still not working. Still complained the same error that: ERROR: "ecisdrdm.application_cdim" is not a known variableLINE 14: MERGE INTO ecisdrdm.application_cdim prod ^SQL state: 42601Character: 348
v/r,
Bach-Nga
As others have said, MERGE is not a command in Postgres. The document you linked is a development document from a long time ago when it was proposed at one time, but it was never accepted. Please reference the actual PostgreSQL documentation.
https://www.postgresql.org/docs/current/index.html
If you need UPSERT like behavior, look at the INSERT ... ON CONFLICT statement.
https://www.postgresql.org/docs/current/sql-insert.html
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
@ 2019-09-24 16:31 George Neuner <gneuner2@comcast.net>
2019-09-24 16:41 ` Re: Merge statement Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: George Neuner @ 2019-09-24 16:31 UTC (permalink / raw)
To: pgsql-admin
On Mon, 23 Sep 2019 15:48:06 +0000 (UTC), Pepe TD Vo <pepevo@yahoo.com>
wrote:
>Yes, I heard MERGE is not a command in Postgres but the others said
>merge statement added stored procedures in version 11
>
>I upgraded my postgresql from 10.6 to 11.1. in AWS, still not work.
>thank you,
>Bach-Nga
You misunderstood.
Postrgesql added stored procedures in version 11. Unlike stored
/functions/ (which PG has had forever), stored procedures allow
transactions within their code. This is similar to the stored procedures
in Oracle and it helps with Oracle -> Postgresql conversions.
However, Postgresql does NOT and never did have a MERGE command. The
"documentation" page you discovered was just a proposal for an
implementation that never happened.
Postgresql can detect conflicts on INSERT and perform an UPDATE instead
(see https://www.postgresql.org/docs/current/sql-insert.html). This is
not exactly the same as Oracle's MERGE so you'll have to study it
carefully to make you understand the nuances and be sure it works as
intended in your application ... but it is the closest you can get using
Postgresql.
George
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Merge statement
2019-09-24 16:31 Re: Merge statement George Neuner <gneuner2@comcast.net>
@ 2019-09-24 16:41 ` Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Pepe TD Vo @ 2019-09-24 16:41 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, George Neuner <gneuner2@comcast.net>
Thank you for clarification.
I perform "insert" a new row into a table is the union is not matched and "update" the row when it matched, the script compilation fine.
thank you all 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 success
On Tuesday, September 24, 2019, 12:32:20 PM EDT, George Neuner <gneuner2@comcast.net> wrote:
On Mon, 23 Sep 2019 15:48:06 +0000 (UTC), Pepe TD Vo <pepevo@yahoo.com> wrote:
>Yes, I heard MERGE is not a command in Postgres but the others said
>merge statement added stored procedures in version 11
>
>I upgraded my postgresql from 10.6 to 11.1. in AWS, still not work.
>thank you,
>Bach-Nga
You misunderstood.
Postrgesql added stored procedures in version 11. Unlike stored functions (which PG has had forever), stored procedures allow transactions within their code. This is similar to the stored procedures in Oracle and it helps with Oracle -> Postgresql conversions.
However, Postgresql does NOT and never did have a MERGE command. The "documentation" page you discovered was just a proposal for an implementation that never happened.
Postgresql can detect conflicts on INSERT and perform an UPDATE instead (see https://www.postgresql.org/docs/current/sql-insert.html). This is not exactly the same as Oracle's MERGE so you'll have to study it carefully to make you understand the nuances and be sure it works as intended in your application ... but it is the closest you can get using Postgresql.
George
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2019-09-24 16:41 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-20 17:06 Merge statement Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 18:21 ` Igor Neyman <ineyman@perceptron.com>
2019-09-20 21:33 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-20 21:40 ` Carrie Berlin <berlincarrie@gmail.com>
2019-09-20 22:15 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 05:57 ` Morris de Oryx <morrisdeoryx@gmail.com>
2019-09-21 12:20 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-21 12:50 ` Carrie Berlin <berlincarrie@gmail.com>
2019-09-23 14:34 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-23 14:48 ` Keith <keith@keithf4.com>
2019-09-23 15:48 ` Pepe TD Vo <pepevo@yahoo.com>
2019-09-24 16:31 Re: Merge statement George Neuner <gneuner2@comcast.net>
2019-09-24 16:41 ` Pepe TD Vo <pepevo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox