Oracle like MERGE statement in in Greenplum
Note: There is no oracle like MERGE Statement in Greenplum.
1. GPLOAD support MERGE/UPSERT statement. Refer gpload MODE.
2. As of postgreSQL 9.4 MERGE was not implemented
3. People have developed function in C, PERL, PYTHON and JAVA to use inside Greenplum
4. Greenplum PWX support MERGE.
If PWX Adapter plugin is configured in Informatica, Pl do following for the Session in sequence:
- Edit Session, Go to Mapping Tab.
- Select Writer tab as Greenplum writer from drop down.
- Go to Connectors tab, Select greenplum writer connection specified to a Greenplum DB schema where your table resides.
- Go to Method tab, Select MERGE Option from drop down(INSERT is dispayed by default).
- Go to Match Columns tab, Type the Matching columns based on which you want to Update/insert rest of the columns such that each columns is embedded between double quotes and separated by comma(eg: "employ_id","employ_name" etc).
- Go to Update Columns tab, Type the columns which are needed to be updated based on Matching columns such that each columns is embedded between double quotes and separated by comma(eg: "location","Project_Name")
- Apply & Save the settings & run the session.
Thats it, Informatica will take care of the Merge operation in parallel using gpload.
You can view count of records which are merged from Informatica Monitor.