PostgreSQL 11 Administration Cookbook

How to do it…

First, identify the duplicates using a query such as the following:

CREATE UNLOGGED TABLE dup_cust ASSELECT *FROM custWHERE customerid IN  (SELECT customerid  FROM cust  GROUP BY customerid  HAVING count(*) > 1);

We save the list of duplicates in a separate table because the query can be very slow if the table is big, so we don't want to run it more than once.

An UNLOGGED table can be created with less I/O because it does not write WAL. It is better than a temporary table, because it doesn't disappear if you disconnect and then reconnect. The other side of the coin is that you lose its contents after a crash, but this is not too bad, because if you are using an unlogged table then you are telling PostgreSQL that you are able to ...