pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReplacing a table with constraints
5+ messages / 4 participants
[nested] [flat]
* Replacing a table with constraints
@ 2005-05-13 18:38 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 2 replies; 5+ messages in thread
From: Mark Fenbers @ 2005-05-13 18:38 UTC (permalink / raw)
To: pgsql-sql
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. 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;" <br>
<br>
I'm perplexed. 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--
Attachments:
Mark.Fenbers.vcf (text/x-vcard, 281B) download
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
* Re: Replacing a table with constraints
@ 2005-05-13 18:49 Ing. Jhon Carrillo <jdigital@cantv.net>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 1 reply; 5+ messages in thread
From: Ing. Jhon Carrillo @ 2005-05-13 18:49 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; pgsql-sql
Use Drop table YOUR_TABLE cascade
Jhon Carrillo
Ingeniero en Computación
Caracas - Venezuela
----- Original Message -----
From: Mark Fenbers
To: pgsql-sql@postgresql.org
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;"
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Replacing a table with constraints
@ 2005-05-13 19:55 Mark Fenbers <Mark.Fenbers@noaa.gov>
parent: Ing. Jhon Carrillo <jdigital@cantv.net>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Fenbers @ 2005-05-13 19:55 UTC (permalink / raw)
To: Ing. Jhon Carrillo <jdigital@cantv.net>; +Cc: pgsql-sql
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. 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 Drop
table YOUR_TABLE <strong>cascade</strong></font></div>
<div> </div>
<div><font color="#000000">Jhon Carrillo<br>
Ingeniero en Computación<br>
</font><font color="#000000">Caracas - Venezuela<br>
</font></div>
<div> </div>
<div> </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. 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;" <br>
<br>
I'm perplexed. 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--
Attachments:
Mark.Fenbers.vcf (text/x-vcard, 281B) download
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
* Re: Replacing a table with constraints
@ 2005-05-13 20:03 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2005-05-13 20:03 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: Ing. Jhon Carrillo <jdigital@cantv.net>; pgsql-sql
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
>
> Ing. Jhon Carrillo wrote:
> > Use Drop table YOUR_TABLE cascade
> >
> > Jhon Carrillo
> > Ingeniero en Computación
> > Caracas - Venezuela
> >
> >
> >
> > ----- Original Message -----
> > From: Mark Fenbers
> > To: pgsql-sql@postgresql.org
> > 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;"
> >
> > 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
>
>
> ______________________________________________________________________
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Replacing a table with constraints
@ 2005-05-13 22:43 Ragnar Hafstað <gnari@simnet.is>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
1 sibling, 0 replies; 5+ messages in thread
From: Ragnar Hafstað @ 2005-05-13 22:43 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-05-13 22:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-13 18:38 Replacing a table with constraints Mark Fenbers <Mark.Fenbers@noaa.gov>
2005-05-13 18:49 ` Ing. Jhon Carrillo <jdigital@cantv.net>
2005-05-13 19:55 ` Mark Fenbers <Mark.Fenbers@noaa.gov>
2005-05-13 20:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-05-13 22:43 ` Ragnar Hafstað <gnari@simnet.is>
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