Merge condition in postgresql

* Amit jain (amitjain.bit@gmail.com) wrote:

I am currently migrating database from ORACLE to postgresql but i am stucked
up at one point while creating procedures.
There is a query which has used oracle MERGE condition so how can i change
this query as per posgresql. kindly suggest me its very urgent.

If you're talking about what I think you're talking about, then
basically you need to break up the MERGE into seperate insert/update
steps. You just have to write the queries such that if the record
doesn't exist, it gets inserted, and if it does exist, then it gets
updated. MERGE just allows you to do this in a nicer, somewhat more
efficient, way. If you've got alot of transactions happening around the
same time with the table in question then you may also have to write
your logic to be able to handle a rollback and to try again.

Enjoy,

Stephen

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Stephen Frost
Sent: Monday, February 04, 2008 8:28 AM
To: Amit jain
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Merge condition in postgresql

* Amit jain (amitjain.bit@gmail.com) wrote:

I am currently migrating database from ORACLE to postgresql but i am

stucked

up at one point while creating procedures.
There is a query which has used oracle MERGE condition so how can i

change

this query as per posgresql. kindly suggest me its very urgent.

If you're talking about what I think you're talking about, then
basically you need to break up the MERGE into seperate insert/update
steps. You just have to write the queries such that if the record
doesn't exist, it gets inserted, and if it does exist, then it gets
updated. MERGE just allows you to do this in a nicer, somewhat more
efficient, way. If you've got alot of transactions happening around

the

same time with the table in question then you may also have to write
your logic to be able to handle a rollback and to try again.

Oracle's merge statement isn't all that fun too. It looks great on
paper when building a data warehouse and you have a type-1 dimension.

However, if you have duplicates in the source table (which is extremely
common) and the target has a unique constraint on the natural key
(extremely common), the merge statement will fail.

Oracle checks for the insert or update at the beginning of the statement
so when it gets to the second key value, it will fail.

Example:

SQL> create table customer (id number primary key not null,
2 natural_key number not null,
3 name varchar2(100));

Table created.

SQL> create sequence customer_id_seq;

Sequence created.

SQL> create or replace trigger t_customer_bi before insert on customer
2 for each row when (new.id is null)
3 begin
4 select customer_id_seq.nextval into :new.id from dual;
5 end;
6 /

Trigger created.

SQL> create table stg_customer (natural_key number not null,
2 name varchar2(100));

Table created.

SQL> insert into stg_customer values (1, 'jon');

1 row created.

SQL> insert into stg_customer values (1, 'jon');

1 row created.

SQL> alter table customer add unique (natural_key);

Table altered.

SQL> merge into customer a using stg_customer b on
2 (a.natural_key = b.natural_key)
3 when matched then update set a.name = b.name
4 when not matched then
5 insert (a.natural_key, a.name) values (b.natural_key, b.name);
merge into customer a using stg_customer b on
*
ERROR at line 1:
ORA-00001: unique constraint (JON.SYS_C004125) violated

When I worked with Oracle a lot, I never could use the merge statement
because it really didn't work well.

If you guys develop Merge for PostgreSQL, I highly suggest putting an
"order by" statement in the syntax so if the source has duplicates, it
will insert the first one and then do subsequent updates.

Jon

On Mon, Feb 04, 2008 at 09:44:17AM -0600, Roberts, Jon wrote:

If you guys develop Merge for PostgreSQL, I highly suggest putting an
"order by" statement in the syntax so if the source has duplicates, it
will insert the first one and then do subsequent updates.

I don't think it would meet the standard definition then. The statement
shouldn't be able to to see the effects of itself...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/