How to create a PostgreSQL remove duplicates trigger function

Credit: xkcd
  1. Replace the entire table every time you update the data. This could work in theory, however it does not play nicely with bigger data sets. Also if your data source ever changes, you loose all the data that it has changed.
  2. Place all historic data in the database then run a query to insert the last x days and update the data every x days. This works better as it does not loose old data that has changed, however it does not capture new changes to the data after it first being queried.
  3. Place all historic data in the database then insert the last 30 days and update daily but have a trigger function to remove all duplicates on insert. This allows recent data to be updated while still maintaining old data in the case of a data source change. The problem with this is that it has to query the entire table on every insert command. The bright side is that it works.
  4. The ideal case would be to use webhooks to have the data source tell the data store whenever an individual piece of data has been added, changed, or deleted. The problem is I haven’t figured out how to work with these yet.
Source: xkcd
DELETE FROM job
WHERE key IN
(SELECT key
FROM
(SELECT key,
ROW_NUMBER() OVER( PARTITION BY transactiondate,
departmentnumber,jobcode,activitynumber
ORDER BY key ) AS row_num
FROM job ) t
WHERE t.row_num > 1 );
Source: xkcd
CREATE OR REPLACE FUNCTION remove_dup()
RETURNS trigger AS
$$
BEGIN
DELETE FROM job
WHERE key IN
(SELECT key
FROM
(SELECT key,
ROW_NUMBER() OVER( PARTITION BY transactiondate,
departmentnumber,jobcode,activitynumber
ORDER BY key ) AS row_num
FROM job ) t
WHERE t.row_num > 1 );
RETURN NULL;
END;$$
LANGUAGE 'plpgsql';
Source: Cody Marie Wild
CREATE TRIGGER job_table_change
AFTER INSERT
ON job
FOR EACH STATEMENT
EXECUTE PROCEDURE remove_dup();
Source: Giphy

--

--

--

Working to solve to worlds problems starting with technology in agriculture.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Emery Silberman

Emery Silberman

Working to solve to worlds problems starting with technology in agriculture.

More from Medium

Starting SQL: FULL OUTER JOINs

Stored Procedures in MySQL

SQL Dialects PostgresSQL vs MySQL

Master SQL Queries (in 5 minutes)