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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of A B
Sent: Monday, July 21, 2008 11:51 AM
To: pgsql-general@postgresql.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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general