Reputation: 1254
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
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