Porting Oracle SQL to PostgreSQL - MERGE INTO

Question

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?

There is no simple merge replacement. For reasons, and possible solutions check this blogpost. — user80168
– user80168, Commented Jun 19, 2013 at 12:50
4) connect by --> recursive common table expression: postgresql.org/docs/current/static/queries-with.html — user330315
– user330315, Commented Oct 26, 2013 at 15:22
possible duplicate of Insert, on duplicate update (postgresql) — user330315
– user330315, Commented Oct 26, 2013 at 15:22
Old question, but I'm downvoting because there is no explanation of what MERGE INTO does, and how the OP wants to use it, making it hard to come up with a meaningful answer. — IMSoP
– IMSoP, Commented Oct 26, 2013 at 15:33
Upvoting, because MERGE is a very widely used command in Oracle SQL, and this is a valid question that crops up quite a bit. — David Aldridge
– David Aldridge, Commented Oct 26, 2013 at 17:30

Chris Travers · Accepted Answer · 2013-10-26 15:15:15Z

1

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);

answered Oct 26, 2013 at 15:15

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Porting Oracle SQL to PostgreSQL - MERGE INTO

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related