One Star

insert if not exist for postgreSql

Hello,
I am trying to merge data from mysql to postgreSql.. But I just wanna insert datas if not exist in Target table.
There is no option like "insert if not exist" in tPostgresqlOutput component.
In adition, both table has primary key and this primary keys exactly same for the data which exist in both table..
So I guess there should be a way to avoid to update datas which exist in target table..
Thanks
3 REPLIES
One Star

Re: insert if not exist for postgreSql

Hi ,
In tpostgresqloutput component properties.
Go to the "Action on data" and select the 'insert or update' option from drop down list.
Insert or update working like:
New record insert in target table,
Existing record updated in target table.
source table contain data like:
emp_id | emp_name
101 A
103 B
104 C
105 D
Target table contain data like:
emp_id | emp_name
101 A
103 E

select the 'insert or update' option from drop down list
Execute the job .
output become like: Target table
emp_id | emp_name
101 A
103 B
104 C
105 D
Here 104,105 are new records and 103 emp_name update with B.
Because 103 emp_name is B in source table.
i.e 103 emp_name is updated B in target table.
Regards.
kumar.talend
One Star

Re: insert if not exist for postgreSql

Hello Kumar,
Thank you for your reply. Target table is more up-to date than source table so I dont wanna update datas in target table. I just wanna insert datas if not exist in target table. So thats what I need is below.
source table contain data like:
emp_id | emp_name
101 A
103 B
104 C
105 D
Target table contain data like:
emp_id | emp_name
101 A
103 E

output should be like:
emp_id | emp_name
101 A
103 E
104 C
105 D

Is it possible to this using Talend?
Thanks
One Star

Re: insert if not exist for postgreSql

Hi,

It is possible through Query.
Source Table Name: EMP
Target Table Name: EMP1
Both Table have same schema.
Query :
"select emp_id,emp_name from EMP where emp_id in
((select emp_id from EMP) except (select emp_id from EMP1)"
Write the above query in tpostgresSqlInput Query body:

Regards
kumar.talend