Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 463A3475D42
	for <pgsql-sql@postgresql.org>; Mon, 26 Aug 2002 04:36:53 -0400 (EDT)
Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net
	[194.217.242.92])
	by postgresql.org (Postfix) with ESMTP id 96179475BA8
	for <pgsql-sql@postgresql.org>; Mon, 26 Aug 2002 04:36:51 -0400 (EDT)
Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk)
	by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1)
	id 17jFMi-000P4r-0Y; Mon, 26 Aug 2002 09:36:52 +0100
Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly)
	by linda.lfix.co.uk with esmtp (Exim 3.35 #1 (Debian))
	id 17jFMe-0005i0-00; Mon, 26 Aug 2002 09:36:48 +0100
Subject: Re: Separating data sets in a table
From: Oliver Elphick <olly@lfix.co.uk>
To: Andreas Tille <tillea@rki.de>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
In-Reply-To: 
 <Pine.LNX.4.44.0208260820060.27720-100000@wr-linux02.rki.ivbb.bund.de>
References: 
 <Pine.LNX.4.44.0208260820060.27720-100000@wr-linux02.rki.ivbb.bund.de>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.8 
Date: 26 Aug 2002 09:36:48 +0100
Message-Id: <1030351008.20178.21.camel@linda>
Mime-Version: 1.0
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/347
X-Sequence-Number: 9193

On Mon, 2002-08-26 at 07:26, Andreas Tille wrote:
> Well for sure this might be an option but as I said I receive the data
> in the dump format apropriate to use "COPY <tablemane> FROM <file>".  Would
> you really like to suggest me to split those data sets into single lines?
> Moreover I'm not sure about how to catch the error messages of failed
> COPY statements.

How about this approach:
    
    Create a temporary table (no constraints)
    
        CREATE TEMP TABLE temptable AS
          (SELECT * FROM tablename LIMIT 1);
        DELETE FROM temptable;
    
    Copy all data into the temporary table
    
        COPY temptable FROM 'filepath';
    
    Select from the temporary table all items that satisfy the
    constraints, insert them into the real table and delete them from
    the temporary table:
        
        BEGIN;
        INSERT INTO tablename (SELECT * FROM temptable WHERE ...);
        DELETE FROM temptable WHERE ...;
        COMMIT;
    
    All good data should now be in place.  The temporary table should
    now contain only those items that do not satisfy the constraints for
    the real table.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Seeing then that all these things shall be dissolved, 
      what manner of persons ought ye to be? You ought to
      live holy and godly lives as you look forward to the
      day of God and speed its coming." 
                                II Peter 3:11,12