CODEWITHSUNDEEP
postgresql
KIM
KIM

Reputation: 1254

Porting Oracle SQL to PostgreSQL - MERGE INTO

What I'm mostly doing these days is converting an Oracle SQL database into a PostgreSQL (9.1.6) database.

The most common issues that I tend to come across are as follows:

1. decode --> case when
2. nvl --> coalesce
3. (+) --> outer join
4. connect by prior --> connectby function with tablefunc contrib module.
5. merge into --> ???
etc...

I am currently skimming through the PostgreSQL manual and funnily enough there seems to be no MERGE INTO statement (or any direct substitutions for it).

I am aware that PL/pgSQL could be used as an alternative but that would mean a lot of extra workload for me. Is there something that I'm missing about this?

Upvotes: 0

Views: 656

Answers (1)

Chris Travers
Chris Travers

Reputation: 26474

There is no simple replacement, and certainly not for 9.1.

The best option is to upgrade to 9.2 and look at writeable CTE's. These allow you to multi-stage write operations in a similar manner. For example, you could:

WITH up (UPDATE foo set bar = 'baz' where id > 1000
         returning id)
INSERT INTO foo(id, bar) 
SELECT s, 'baz'
  FROM generate_sequence(1001, 10000) s
 WHERE s NOT IN (select id from up);

Upvotes: 1

Related Questions