I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong:
BEGIN
INSERT INTO suppliers("id","name", "url", "logo", "clicks", "order")
SELECT * FROM dblink('dbname=linkeddatabase', 'SELECT id,"name", url, logo, clicks, "order" FROM suppliers')
AS supp(id int,"name" character varying, "url" character varying, "logo" character varying, "clicks" int, "order" int);
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
Problem:
It looks like it stops as soon as it reaches a duplicated row. Cause as soon as the function is finished, my table contains the same amount of rows...
What I want: If it needs to insert 1000 rows, of which the first 100 are duplicated, it still needs to insert the last 900 rows. Any ideas to fix this with the current function? Or do I just need to create some loop?
INSERT INTO suppliers ... SELECT ... FROM dblink(...) t WHERE NOT EXISTS (SELECT 1 FROM suppliers s WHERE s.id = t.id
or something like that. See dba.stackexchange.com/questions/30499/…