Date: Tue, 6 Feb 2007 17:22:38 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Removing duplicate rows
Message-ID: <20070206222238.GA28647@phlogiston.dyndns.org>
References: <45C8FDE6.6050603@autoledgers.com.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45C8FDE6.6050603@autoledgers.com.au>
User-Agent: Mutt/1.5.9i

On Wed, Feb 07, 2007 at 07:15:02AM +0900, Paul Lambert wrote:
> 
> I'm assuming the best way to get around this is to load the data into a 
> temporary table with "copy from" and then do a "select distinct into" my 
> real table.

You might find that sort|uniq at the command prompt would be better. 
That said,

> script to create the table as a copy of the existing 'real' table. I'm 
> assuming "create table as" would be what I need to use, but what would I 
> use as the select sql - i.e. how do I use select to pull the definition 
> of a table.

an easy way to do this is "CREATE TABLE name AS SELECT . . . WHERE
1=0".  You get a table with no rows.  (WHERE FALSE and similar
constructs all work equally well.)

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
                --Brad Holland