INSERT IGNORE equivalent for Vertica?
Our data input stream has a lot of duplicates that need to be filtered on the database leayer.
We are using a staging table without constraints and a deduplicated table which is periodically created via
CREATE TABLE IF NOT EXISTS tableName_dedup AS SELECT column1, column2, ... FROM (SELECT *, row_number OVER (PARTITION BY column1, column2) as rownum FROM tableName_import) import where import.rownum = 1;
This however takes forever, because the SELECT has poor performance. It would be much faster to simply ignore the duplicates and only insert the first one.
I can't seem to get it to work via a MERGE, because there is no "ON MATCHED IGNORE" or similar.
Is there a way to do this?