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 Commented Apr 14, 2015 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. Commented Apr 15, 2015 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? Commented Apr 15, 2015 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
    Commented Apr 15, 2015 at 18:15

1 Answer 1


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.

  • 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 Commented Jun 25, 2020 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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