Message-ID: <4295FA5C.6030209@ca.afilias.info>
Date: Thu, 26 May 2005 12:33:32 -0400
From: Andrew Hammond <ahammond@ca.afilias.info>
Organization: Afilias Canada Corp.
User-Agent: Debian Thunderbird 1.0.2 (X11/20050331)
MIME-Version: 1.0
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Duplicated records
References: <20050524175931.4dp41vy7v10c4wc4@www.presserv.org>
	<20050525050346.GA29126@webserv.wug-glas.de>
	<20050525135807.x9p08co04v8gg0cw@www.presserv.org>
	<4294B149.3040207@PresiNET.com>
	<20050525175356.s6rowsmck8e888s8@www.presserv.org>
In-Reply-To: <20050525175356.s6rowsmck8e888s8@www.presserv.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

lucas@presserv.org wrote:
> Thanks....
> CTAS (Create Table As Select) command works fine!!! With great performance.
> I think it is the best way to correct the data...(apparently)
> I didnt know about "select DISTINCT". I am going to read about it.

You might want to wrap this in some locking and throw in some
constraints to avoid dupes in the future...

BEGIN;
LOCK lanctos IN ACCESS EXCLUSIVE;
CREATE TABLE lanctos_distinct AS SELECT DISTINCT * FROM lanctos;
DROP TABLE lanctos;
ALTER TABLE lanctos_distinct RENAME TO lanctos;
ALTER TABLE lanctos ALTER id SET NOT NULL;
CREATE UNIQUE INDEX lanctos_id_idx ON lanctos (id);
ALTER TABLE lanctos ADD CONSTRAINT lanctos_id_pkey PRIMARY KEY (id);
COMMIT;

As always, don't forget to ANALYZE the new table.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFClfpbgfzn5SevSpoRAnj3AJ9xvCmMiC9yWNmS9XLFZWO3o4vNcACfboz+
T442LzdAAV1DbIoj24rCJeA=
=vrDU
-----END PGP SIGNATURE-----