I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses:

          Column           |            Type             |                        Modifiers
id                        | integer                     | not null default nextval('properties_id_seq'::regclass)
street_number             | character varying(255)      |
street_name               | character varying(255)      |
street_direction          | character varying(255)      |
unit_number               | character varying(255)      |
city_id                   | integer                     |
postal_code               | character varying(255)      |
year_built                | integer                     |
floor_area                | integer                     |
bedroom                   | integer                     |
bathroom                  | numeric                     |
lot_size                  | integer                     |
den                       | integer                     |

The duplicates are based on the addresses containing of street_number, street_name, street_direction, unit_number and city_id. The table has additional information such as # of beds and # of baths etc. This additional information is not populated for all rows of the table, which makes removing the duplicates tricky as I do not want to lose any information. I would like to mark the duplicates with the least amount of information for deletion (add a field with 1 for delete and 0 for keep). I can see 2 ways of doing this:

  1. Duplicate the information of the entry with the complete information to the other entries and randomly mark all but one entry for deletion
  2. Identify the row with the most information and mark the other rows for deletion.

Ideally, I would like to just identify the rows with the most information and then delete the other rows. Is there anybody who could help me with a query for this?

  • blog.databasepatterns.com/2014/12/… . Instead of two separate tables, join to self – Neil McGuigan Apr 14 '15 at 0:43
  • 1
    The usual suspects are missing: table definition (what you get from \d tbl in psql, at least a minimal version) and your version of Postgres. – Erwin Brandstetter Apr 15 '15 at 2:02
  • Are you sure you want to just delete the row with less information? What if one row has # of beds and the other has # of baths? – Colin 't Hart Apr 15 '15 at 12:37
  • You are right @Colin'tHart. I will need to double check if the additional information is distributed over several rows, although this is a very unlikely scenario. – Anne Apr 15 '15 at 18:15

Your question is a bit light on definitions. Assuming:

  • You define the "least amount of information" with how many of the relevant columns are NULL.
  • Primary key is adr_id.
  • Duplicates are marked with a column dupe_id to indicate groups of duplicates.

Since it's also vague what to do exactly, I create a separate table with the dupe ranking:

CRATE TABLE adr_dupe_rank AS
SELECT adr_id, dupe_id
     , rank() OVER (PARTITION BY dupe_id
                    ORDER BY (nr_of_beds  IS NULL)::int
                           + (nr_of_baths IS NULL)::int
                        -- + (...)::int  -- more terms
                   ) AS rnk
FROM   address;

false translate to 0, true translates to 1. So rows with the fewest empty columns are ranked first. Master rows end up with rnk = 1. Dupes get higher rnk numbers.

The window function rank() assigns 1 to all rows sharing the lowest score per dupe_id. Add enough columns or other terms to break ties and get one winner per dupe_id. Or deal with multiple winners separately.

You can then do as you please. To just delete dupes:

DELETE FROM address a
USING  adr_dupe_rank d
WHERE  a.adr_id = d.adr_id
AND    d.rnk > 1;

Alternatively you can use the above query as derived table in the DELETE directly.

| improve this answer | |
  • You can also break ties by generating a uuid on the fly in ORDER BY. I needed to do this in my case as there was no primary key in my table. E.g. SELECT *, rank() OVER (PARTITION BY uprn ORDER BY parking_spaces DESC, uuid_generate_v4() ) AS rnk FROM sandbox.dummy_dupes – DeadPassive Jun 25 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.