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
Date: Fri, 13 May 2005 15:03:51 -0500

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