From Mark.Fenbers@noaa.gov Fri May  13 18:38:38 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7D0BE52A73
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 15:38:38 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 96857-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 18:38:32 +0000 (GMT)
Received: from werhmsg.werh.noaa.gov (werhmsg.werh.noaa.gov [198.206.32.236])
	by svr1.postgresql.org (Postfix) with ESMTP id 12A92533CF
	for <pgsql-sql@postgresql.org>; Fri, 13 May 2005 15:38:29 -0300 (ADT)
Received: from [198.206.42.50] ([198.206.42.50]) by
	werhmsg.werh.noaa.gov (Netscape Messaging Server 4.15) with
	ESMTP id IGFYG300.741 for <pgsql-sql@postgresql.org>; Fri, 13
	May 2005 14:38:27 -0400
Message-ID: <4284F421.10809@noaa.gov>
Date: Fri, 13 May 2005 14:38:25 -0400
From: "Mark Fenbers" <Mark.Fenbers@noaa.gov>
Organization: DoC/NOAA/NWS/OHRFC
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Replacing a table with constraints
Content-Type: multipart/mixed; boundary="------------000607070806050505090509"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.302 tagged_above=0 required=5 tests=AWL, HTML_20_30,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200505/98
X-Sequence-Number: 21411

This is a multi-part message in MIME format.
--------------000607070806050505090509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#333399">
I have a table called Counties which partially contains a lot bad
data.&nbsp; By" bad data", I mean some records are missing; some exist and
shouldn't; and some records have fields with erroneous information.&nbsp;
However, the majority of the data in the table is accurate.&nbsp; I have
built/loaded a new table called newCounties with the same structure as
Counties, but contains no bad data.&nbsp; My was&nbsp; to completely replace the
contents of Counties with the contents of newCounties.&nbsp; The problem is:
several other tables have Foreign Key constraints placed on Counties.&nbsp;
Therefore, Pg will not let me 'DELETE FROM Counties;", nor will it let
me "DROP TABLE Counties;"&nbsp; <br>
<br>
I'm perplexed.&nbsp; Can someone suggest how I can best get data from
Counties to look just like newCounties?<br>
<br>
Mark<br>
</body>
</html>

--------------000607070806050505090509
Content-Type: text/x-vcard; charset=utf-8;
 name="Mark.Fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="Mark.Fenbers.vcf"

begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:DoC/NOAA/NWS/OHRFC
adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430 x246
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard


--------------000607070806050505090509--



From jdigital@cantv.net Fri May  13 18:50:50 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E071E53347
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 15:50:50 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 99519-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 18:50:46 +0000 (GMT)
Received: from rs25s9.datacenter.cha.cantv.net
	(rs25s9.datacenter.cha.cantv.net [200.44.33.10])
	by svr1.postgresql.org (Postfix) with ESMTP id D396553238
	for <pgsql-sql@postgresql.org>; Fri, 13 May 2005 15:50:45 -0300 (ADT)
Received: from tgusta2 (dC85A71E0.dslam-21-1-18-10-2-02.fsl.dsl.cantv.net
	[200.90.113.224])
	by rs25s9.datacenter.cha.cantv.net (8.13.0/8.13.0/3.0) with SMTP id
	j4DInKpr012482; Fri, 13 May 2005 14:50:08 -0400
X-Matched-Lists: []
Message-ID: <01c301c557ec$9983b090$1400a8c0@tgusta2>
Reply-To: "Ing. Jhon Carrillo" <jdigital@cantv.net>
From: "Ing. Jhon Carrillo" <jdigital@cantv.net>
To: "Mark Fenbers" <Mark.Fenbers@noaa.gov>,
	<pgsql-sql@postgresql.org>
References: <4284F421.10809@noaa.gov>
Subject: Re: Replacing a table with constraints
Date: Fri, 13 May 2005 14:49:38 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_01C0_01C557CA.FD041280"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: ClamAV version 0.84, clamav-milter version 0.84e on
	rs25s9.datacenter.cha.cantv.net
X-Virus-Status: Clean
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.264 tagged_above=0 required=5 tests=AWL, HTML_50_60,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200505/99
X-Sequence-Number: 21412

This is a multi-part message in MIME format.

------=_NextPart_000_01C0_01C557CA.FD041280
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Use          Drop table YOUR_TABLE cascade

Jhon Carrillo
Ingeniero en Computaci=F3n
Caracas - Venezuela



----- Original Message -----=20
  From: Mark Fenbers=20
  To: pgsql-sql@postgresql.org=20
  Sent: Friday, May 13, 2005 2:38 PM
  Subject: [SQL] Replacing a table with constraints


  I have a table called Counties which partially contains a lot bad =
data.  By" bad data", I mean some records are missing; some exist and =
shouldn't; and some records have fields with erroneous information.  =
However, the majority of the data in the table is accurate.  I have =
built/loaded a new table called newCounties with the same structure as =
Counties, but contains no bad data.  My was  to completely replace the =
contents of Counties with the contents of newCounties.  The problem is: =
several other tables have Foreign Key constraints placed on Counties.  =
Therefore, Pg will not let me 'DELETE FROM Counties;", nor will it let =
me "DROP TABLE Counties;" =20

  I'm perplexed.  Can someone suggest how I can best get data from =
Counties to look just like newCounties?

  Mark



-------------------------------------------------------------------------=
-----



  ---------------------------(end of =
broadcast)---------------------------
  TIP 8: explain analyze is your friend


------=_NextPart_000_01C0_01C557CA.FD041280
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type =
content=3Dtext/html;charset=3DISO-8859-1>
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY text=3D#333399 bgColor=3D#ffffff>
<DIV><FONT face=3DArial color=3D#000000=20
size=3D2>Use&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Drop =
table=20
YOUR_TABLE <STRONG>cascade</STRONG></FONT></DIV>
<DIV><FONT face=3DArial color=3D#000000 size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#000000>Jhon Carrillo<BR>Ingeniero en=20
Computaci=F3n<BR></FONT><FONT color=3D#000000>Caracas - =
Venezuela<BR></FONT></DIV>
<DIV><FONT face=3DArial color=3D#000000 size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial color=3D#000000 size=3D2></FONT>&nbsp;</DIV>
<DIV>----- Original Message ----- </DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
  <A title=3DMark.Fenbers@noaa.gov =
href=3D"mailto:Mark.Fenbers@noaa.gov">Mark=20
  Fenbers</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3Dpgsql-sql@postgresql.org=20
  href=3D"mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> =
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, May 13, 2005 2:38 =
PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] Replacing a =
table with=20
  constraints</DIV>
  <DIV><BR></DIV>I have a table called Counties which partially contains =
a lot=20
  bad data.&nbsp; By" bad data", I mean some records are missing; some =
exist and=20
  shouldn't; and some records have fields with erroneous =
information.&nbsp;=20
  However, the majority of the data in the table is accurate.&nbsp; I =
have=20
  built/loaded a new table called newCounties with the same structure as =

  Counties, but contains no bad data.&nbsp; My was&nbsp; to completely =
replace=20
  the contents of Counties with the contents of newCounties.&nbsp; The =
problem=20
  is: several other tables have Foreign Key constraints placed on=20
  Counties.&nbsp; Therefore, Pg will not let me 'DELETE FROM Counties;", =
nor=20
  will it let me "DROP TABLE Counties;"&nbsp; <BR><BR>I'm =
perplexed.&nbsp; Can=20
  someone suggest how I can best get data from Counties to look just =
like=20
  newCounties?<BR><BR>Mark<BR>
  <P>
  <HR>

  <P></P><BR>---------------------------(end of=20
  broadcast)---------------------------<BR>TIP 8: explain analyze is =
your=20
  friend<BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_01C0_01C557CA.FD041280--





From Mark.Fenbers@noaa.gov Fri May  13 19:55:51 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8EBCA53613
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 16:55:51 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 10274-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 19:55:45 +0000 (GMT)
Received: from werhmsg.werh.noaa.gov (werhmsg.werh.noaa.gov [198.206.32.236])
	by svr1.postgresql.org (Postfix) with ESMTP id 8FE1C536AD
	for <pgsql-sql@postgresql.org>; Fri, 13 May 2005 16:55:43 -0300 (ADT)
Received: from [198.206.42.50] ([198.206.42.50]) by
	werhmsg.werh.noaa.gov (Netscape Messaging Server 4.15) with
	ESMTP id IGG20X00.D4A; Fri, 13 May 2005 15:55:45 -0400
Message-ID: <42850640.9090309@noaa.gov>
Date: Fri, 13 May 2005 15:55:44 -0400
From: "Mark Fenbers" <Mark.Fenbers@noaa.gov>
Organization: DoC/NOAA/NWS/OHRFC
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: "Ing. Jhon Carrillo" <jdigital@cantv.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Replacing a table with constraints
References: <4284F421.10809@noaa.gov> <01c301c557ec$9983b090$1400a8c0@tgusta2>
In-Reply-To: <01c301c557ec$9983b090$1400a8c0@tgusta2>
Content-Type: multipart/mixed; boundary="------------060608000603070007090408"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.114 tagged_above=0 required=5 tests=AWL, HTML_60_70,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200505/100
X-Sequence-Number: 21413

This is a multi-part message in MIME format.
--------------060608000603070007090408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#333399">
True, but Counties has about 8 or 9 rules, view, or pk constraints
attached to it.&nbsp; I don't want to break all these unless I knew of a way
to save off the SQL for them beforehand so I can easily rebuild them...<br>
Mark<br>
<br>
Ing. Jhon Carrillo wrote:
<blockquote cite="mid01c301c557ec$9983b090$1400a8c0@tgusta2" type="cite">
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <meta content="MSHTML 6.00.2600.0" name="GENERATOR">
  <style></style>
  <div><font color="#000000" face="Arial" size="2">Use&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Drop
table YOUR_TABLE <strong>cascade</strong></font></div>
  <div>&nbsp;</div>
  <div><font color="#000000">Jhon Carrillo<br>
Ingeniero en Computaci&oacute;n<br>
  </font><font color="#000000">Caracas - Venezuela<br>
  </font></div>
  <div>&nbsp;</div>
  <div>&nbsp;</div>
  <div>----- Original Message ----- </div>
  <blockquote
 style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;">
    <div
 style="background: rgb(228, 228, 228) none repeat scroll 0%; -moz-background-clip: initial; -moz-background-origin: initial; -moz-background-inline-policy: initial; font-family: arial; font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"><b>From:</b>
    <a title="Mark.Fenbers@noaa.gov" href="mailto:Mark.Fenbers@noaa.gov">Mark
Fenbers</a> </div>
    <div
 style="font-family: arial; font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"><b>To:</b>
    <a title="pgsql-sql@postgresql.org"
 href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a> </div>
    <div
 style="font-family: arial; font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"><b>Sent:</b>
Friday, May 13, 2005 2:38 PM</div>
    <div
 style="font-family: arial; font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"><b>Subject:</b>
[SQL] Replacing a table with constraints</div>
    <div><br>
    </div>
I have a table called Counties which partially contains a lot bad
data.&nbsp; By" bad data", I mean some records are missing; some exist and
shouldn't; and some records have fields with erroneous information.&nbsp;
However, the majority of the data in the table is accurate.&nbsp; I have
built/loaded a new table called newCounties with the same structure as
Counties, but contains no bad data.&nbsp; My was&nbsp; to completely replace the
contents of Counties with the contents of newCounties.&nbsp; The problem is:
several other tables have Foreign Key constraints placed on Counties.&nbsp;
Therefore, Pg will not let me 'DELETE FROM Counties;", nor will it let
me "DROP TABLE Counties;"&nbsp; <br>
    <br>
I'm perplexed.&nbsp; Can someone suggest how I can best get data from
Counties to look just like newCounties?<br>
    <br>
Mark<br>
    <p> </p>
    <hr> <br>
---------------------------(end of broadcast)---------------------------<br>
TIP 8: explain analyze is your friend<br>
  </blockquote>
</blockquote>
</body>
</html>

--------------060608000603070007090408
Content-Type: text/x-vcard; charset=utf-8;
 name="Mark.Fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="Mark.Fenbers.vcf"

begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:DoC/NOAA/NWS/OHRFC
adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430 x246
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard


--------------060608000603070007090408--



From smarlowe@g2switchworks.com Fri May  13 20:04:02 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 25C22535E8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 17:04:02 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 11643-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 20:03:56 +0000 (GMT)
Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25])
	by svr1.postgresql.org (Postfix) with ESMTP id D9990534FE
	for <pgsql-sql@postgresql.org>; Fri, 13 May 2005 17:03:54 -0300 (ADT)
Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP
	with MS-WebStorage 6.5.6944
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	13 May 2005 15:03:51 -0500
Subject: Re: Replacing a table with constraints
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Cc: "Ing. Jhon Carrillo" <jdigital@cantv.net>,
	pgsql-sql@postgresql.org
In-Reply-To: <42850640.9090309@noaa.gov>
References: <4284F421.10809@noaa.gov>
	<01c301c557ec$9983b090$1400a8c0@tgusta2>  <42850640.9090309@noaa.gov>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <1116014631.22291.52.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Fri, 13 May 2005 15:03:51 -0500
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/101
X-Sequence-Number: 21414

Are the constraints deferrable?

If they are, then you can replace the data with a single transaction.

If not, then you'll have to look at disabling triggers for the update.

On Fri, 2005-05-13 at 14:55, Mark Fenbers wrote:
> True, but Counties has about 8 or 9 rules, view, or pk constraints
> attached to it.  I don't want to break all these unless I knew of a
> way to save off the SQL for them beforehand so I can easily rebuild
> them...
> Mark
>=20
> Ing. Jhon Carrillo wrote:=20
> > Use          Drop table YOUR_TABLE cascade
> > =20
> > Jhon Carrillo
> > Ingeniero en Computaci=C3=B3n
> > Caracas - Venezuela
> >=20
> > =20
> > =20
> > ----- Original Message -----=20
> >         From: Mark Fenbers
> >         To: pgsql-sql@postgresql.org
> >         Sent: Friday, May 13, 2005 2:38 PM
> >         Subject: [SQL] Replacing a table with constraints
> >        =20
> >         I have a table called Counties which partially contains a
> >         lot bad data.  By" bad data", I mean some records are
> >         missing; some exist and shouldn't; and some records have
> >         fields with erroneous information.  However, the majority of
> >         the data in the table is accurate.  I have built/loaded a
> >         new table called newCounties with the same structure as
> >         Counties, but contains no bad data.  My was  to completely
> >         replace the contents of Counties with the contents of
> >         newCounties.  The problem is: several other tables have
> >         Foreign Key constraints placed on Counties.  Therefore, Pg
> >         will not let me 'DELETE FROM Counties;", nor will it let me
> >         "DROP TABLE Counties;" =20
> >        =20
> >         I'm perplexed.  Can someone suggest how I can best get data
> >         from Counties to look just like newCounties?
> >        =20
> >         Mark
> >        =20
> >        =20
> >        =20
> >         ____________________________________________________________
> >         ---------------------------(end of
> >         broadcast)---------------------------
> >         TIP 8: explain analyze is your friend
>=20
>=20
> ______________________________________________________________________
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>=20
>                http://archives.postgresql.org



From gnari@simnet.is Fri May  13 22:35:23 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6113853411
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 19:35:23 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 35730-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 13 May 2005 22:35:21 +0000 (GMT)
Received: from quasar.skima.is (quasar.skima.is [212.30.200.205])
	by svr1.postgresql.org (Postfix) with ESMTP id 3339B53443
	for <pgsql-sql@postgresql.org>; Fri, 13 May 2005 19:35:18 -0300 (ADT)
Received: from gnari ([85.220.28.44] [85.220.28.44]) by quasar.skima.is with
	ESMTP; Fri, 13 May 2005 22:35:17 Z
Subject: Re: Replacing a table with constraints
From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4284F421.10809@noaa.gov>
References: <4284F421.10809@noaa.gov>
Content-Type: text/plain
Date: Fri, 13 May 2005 22:43:19 +0000
Message-Id: <1116024199.11747.37.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/102
X-Sequence-Number: 21415

On Fri, 2005-05-13 at 14:38 -0400, Mark Fenbers wrote:
> I have a table called Counties which partially contains a lot bad
> data.  By" bad data", I mean some records are missing; some exist and
> shouldn't; and some records have fields with erroneous information.
> However, the majority of the data in the table is accurate.  I have
> built/loaded a new table called newCounties with the same structure as
> Counties, but contains no bad data.  My was  to completely replace the
> contents of Counties with the contents of newCounties.  The problem
> is: several other tables have Foreign Key constraints placed on
> Counties.  Therefore, Pg will not let me 'DELETE FROM Counties;", nor
> will it let me "DROP TABLE Counties;"  
> 
> I'm perplexed.  Can someone suggest how I can best get data from
> Counties to look just like newCounties?

assuming same primary key on these 2 tables, you have to
consider these cases:

a) records in Counties not in newCounties
b) records in newCounties existing in Counties, but different
c) records in newCounties also correct in Counties
d) records in newCounties missing from Counties

a) get rid of extra records:
       delete from Counties 
              where primarykey not in 
                 (select primarykey from newCounties);
   if you hit foreign key constrains, you need to look
   at your data a bit more, anyways 
 
b) update erroneous records:
       update Counties from newCounties set 
            col1= newCounties.col1,
            col2= newCounties.col2,
            col3= newCounties.col3,
            ...
       from newCounties
       where primarykey=newCounties.primarykey
            and
            (    Counties.col1 <> newCounties.col1
              OR Counties.col2 <> newCounties.col2
              OR Counties.col3 <> newCounties.col3
              ... );

c) nothing to do here

d) insert into Counties 
       select * from newCounties
           where primarykey not in
              (select primarykey from Counties);



test case:

test=# create table c (p int, col1 int, col2 int);
CREATE TABLE
test=# insert into c values (1,1,1); -- correct values
INSERT 7693959 1
test=# insert into c values (2,1,2); -- incorrect
INSERT 7693960 1
test=# insert into c values (9,9,9); -- extra value
INSERT 7693961 1
test=# create table newc (p int, col1 int, col2 int);
CREATE TABLE
test=# insert into newc values (1,1,1); -- correct values
INSERT 7693964 1
test=# insert into newc values (2,2,2); -- incorrect in c
INSERT 7693965 1
test=# insert into newc values (3,3,3); -- missing in c
INSERT 7693966 1
test=# delete from c where p not in (select p from newc);
DELETE 1
test=# update c set col1=n.col1, col2=n.col2
test-#   from newc as n
test-#   where c.p=n.p and (c.col1<>n.col1 or c.col2<>n.col2);
UPDATE 1
test=# insert into c select * from newc where p not in (select p from
c);
INSERT 7693967 1
test=# select * from c;
 p | col1 | col2
---+------+------
 1 |    1 |    1
 2 |    2 |    2
 3 |    3 |    3
(3 rows)


note: 
b) and c) can be merged and simplified if you don't mind updating
unchanged records:
       update Counties from newCounties set 
            col1= newCounties.col1,
            col2= newCounties.col2,
            col3= newCounties.col3,
            ...
       from newCounties
       where primarykey=newCounties.primarykey;



gnari