Is MERGE supported in Greenplum Database 4.3.5.1 build 1

Question

I am trying to create a merge statement for Greenplum DB and I am getting an syntax error. So I am wondering if the MERGE is even supported the way I am writing it.

I have two approaches Approach 1-

MERGE into public.table20 pritab 
USING 
(
    select stgout.key1, stgout.key2, stgout.col1
    from public.table20_stage stgout 
    where stgout.sequence_id < 1000
) as stgtab
ON (pritab.key1 = stgtab.key1
and pritab.key2 = stgtab.key2)
WHEN MATCHED THEN
UPDATE SET pritab.key1 = stgtab.key1 
,pritab.key2 = stgtab.key2
,pritab.col1    = stgtab.col1
WHEN NOT MATCHED THEN
INSERT  (key1, key2, col1)
values (stgtab.key1, stgtab.key2, stgtab.col1);

Approach 2:

public.table20 pritab 
SET pritab.key1 = stgtab.key1 
    ,pritab.key2 = stgtab.key2
    ,pritab.col1    = stgtab.col1
from 
(
        select stgout.key1, stgout.key2, stgout.col1
        from public.table20_stage stgout
        where stgout.sequence_id < 1000
) as stgtab
   ON (pritab.key1 = stgtab.key1
    and pritab.key2 = stgtab.key2)
returning (stgtab.key1, stgtab.key2, stgtab.col1);

Is there any other way or something is wrong with my syntax itself?

Answer 1

Merge is not supported in Greenplum but I wrote a blog post on how to achieve the results of a merge statement in Greenplum.

http://www.pivotalguru.com/?p=104