From: Andrew Hammond <ahammond@ca.afilias.info>
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Duplicated records
Date: Thu, 26 May 2005 12:33:32 -0400
Message-ID: <4295FA5C.6030209@ca.afilias.info> (raw)
In-Reply-To: <20050525175356.s6rowsmck8e888s8@www.presserv.org>
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>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ahammond@ca.afilias.info, lucas@presserv.org
Subject: Re: Duplicated records
In-Reply-To: <4295FA5C.6030209@ca.afilias.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox