Re: Merge condition in postgresql

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Stephen Frost
> Sent: Monday, February 04, 2008 8:28 AM
> To: Amit jain
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Merge condition in postgresql
>
> * Amit jain (amitjain(dot)bit(at)gmail(dot)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