Re: How to remove duplicate lines but save one of the lines?

minor refinement on suggestion:

-- CTAS (create table as) is easiest way to create table with same
structure
create table foo as select * from orig_table;
-- truncate is much more efficient than delete
truncate orig_table;
-- unchanged
insert into orig_table select * from foo;
-- recompute statistics
analyze orig_table

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of A B
Sent: Monday, July 21, 2008 11:51 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to remove duplicate lines but save one of the
lines?

> There is probably a more elegant way of doing it, but a simple way of
doing
> it ( depending on the size of the table ) could be:
>
> begin;
>
> insert into foo select distinct * from orig_table;
> delete from orig_table;
> insert into orig_table select * from foo;
>
> commit;

Just to make it clear to me
Here foo is a table that I have to create with the command
CREATE TABLE foo (....same columns as orig_table);
?

Is it possible to add a unique constraint to the table, with a
"delete" option so it will delete duplicates?