large database with duplicates

[jasondavis]

I have a database with just emails

the row name is "address"

Basicly there is around 700,000 rows in this DB and it is around 20mb

How can I delete all duplicate entries?

[Piranha]

Probably the easiest thing to do is to create a new table, insert the unique addresses there, delete the old table and rename the new one.

[Sxooter]

Do you have a column with an id or something in it that's unique? If not, then create one and give it incrementing numbers. Here's an example:

create table emails (id serial, email text); -- serial is postgresql's autoinc type...
insert into emails (email) values ('[e-mail]');
insert into emails (email) values ('[e-mail]');
insert into emails (email) values ('[e-mail]');
select * from emails;
 id |     email
----+---------------
  1 | [e-mail]
  2 | [e-mail]
  3 | [e-mail]
(3 rows)

select e1.id from emails e1 join emails e2 on (e1.email=e2.email and e1.id > e2.id);
 id
----
  3
(1 row)

delete from emails where id in (
    select e1.id 
    from emails e1 
    join emails e2 
    on (e1.email=e2.email and e1.id > e2.id)
);

select * from emails;
 id |     email
----+---------------
  1 | [e-mail]
  2 | [e-mail]

See what I did there with the join and the e1.id>e2.id?