Re: Deleting one of 2 identical records

On 7 September 2011 00:55, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:

>
> Hello Thom.
>
> what is the meaning of
>
> select table_name from table_name
>
> Also is this a common behavior of all Databases i.e. oracle , Microsoft
> ,...etc . i.e is this is the standard behavior
>
> I think this is a good way to find duplicates in general, I will write a
> routine to compare all the columns by excluding the primary key which is
> serial
>
> Thanks in advance
>

Well I just put table_name as a placeholder for the actual name of the table
you wish to remove duplicates from.

No, you can't run this query on other databases, particularly because ctids
are specific to PostgreSQL. Other databases will use different methods to
this one, if they have any at all. With Oracle you'd probably use ROWNUM
somehow, and SQL Server will likely use some awful multi-query technique
involving creating temporary tables, copying distinct rows from the
duplicate set to another table, deleting it from the original and copying
back. Can't say for sure though since I haven't used it in quite a while.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company