Re: Re[2]: [SQL] Query to eliminate duplicates - Mailing list pgsql-sql

From Leslie Mikesell
Subject Re: Re[2]: [SQL] Query to eliminate duplicates
Date
Msg-id 199812032002.OAA26371@Mercury.mcs.net
Whole thread Raw
In response to RE: Re[2]: [SQL] Query to eliminate duplicates  (Mario Filipe <mjnf@uevora.pt>)
List pgsql-sql
According to Mario Filipe:
>
> >>> MF> Is there a way to eliminate duplicate records using just SQL?
> >>>
> >>> SELECT DISTINCT ...
> >
> >
> > MF>         The problem is that i have a table where there are duplicate
> > records
> > MF> and i want to delete them! I tryed creating a unique index but it told me
> > it
> > MF> couldn't because it didn't have a function to do something (I think it is
> > MF> because i had a boolean field in it)
>
> So a duplicate is actually a record that looks exactly like other record on the
> table...

How about:

select distinct * into newtable from oldtable ;
drop table oldtable;
alter table newtable rename to oldtable;

You have to drop/create indexes separately, even the ones built
implicitly from 'primary key' directives.

   Les Mikesell
    les@mcs.com

pgsql-sql by date:

Previous
From: Barracuda
Date:
Subject: RE: Re[2]: [SQL] Please help: How to determine largest of two num bers in a query?
Next
From: Bruce Momjian
Date:
Subject: Re: [SQL] Optimizations