How to remove duplicates and keep the one with the most additional information 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:


Duplicate the information of the entry with the complete information to the other entries and randomly mark all but one entry for deletion
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?
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 &gt; 1;


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