Duplicate Data entry problem

Started by James Hallabout 23 years ago2 messagesgeneral
Jump to latest
#1James Hall
James.Hall@RadioShack.com

Hello,

Using Postgresql version 7.1 and having a problem with data being inserted
into 2 tables when it should only be going into 1.

1st I created several new tables using a template.

# create the new table
my $SQL = "CREATE TABLE \"$newtable\" () inherits
(\"template2\") ";
my $stmt_handle = $dbh->prepare($SQL);
my $rows = $stmt_handle->execute;
$stmt_handle->finish();

But when I insert data into a table, it also gets added into another table.

my $SQL="INSERT INTO \"$series\" ( \"PrintOrder\", \"Description\",
\"FullText\",
\"Obsolete\", \"Author\", \"Comments\", \"Limits\",
\"AuditRequired\",
\"Selections\", \"SelectionList\", \"RadioOption\",
\"CheckOption\", \"Locked\")
VALUES ( '$printorder', '$description', '$fulltext',
'$obsolete',
'$author', '$comments', '$limits', '$audit', '$selections',
'$selectionlist',
'$radio', '$check', '$locked')";
my $stmt_handle=$dbh->prepare($SQL);
my $rows=$stmt_handle->execute;
$stmt_handle->finish();

This results in the data being added to the correct table, but also to
another table:

For example. If I add a line to the table "workmanship", the line also
appears in the table "acpower".

If I then delete the line from "workmanship" it is also gone from "acpower"

If I run: EXPLAIN SELECT "PrintOrder" from "acpower"; I get:
NOTICE: QUERY PLAN

Result (cost-0.00..3.27 rows=28 width=12)
-> Append (cost=0.00..3.27 rows=28 width=12)
-> Seq Scan on acpower (cost=0.00..1.13 rows=13 width=12)
-> Seq Scan on template2 acpower (cost=0.00..0.00 rows=1 width=12)
-> Seq Scan on General Requirements acpower (cost=0.00..1.11
rows=11 width=12)
-> Seq Scan on workmanship acpower (cost0.00..1.03 rows=3
width=12)

It appears to me that it is linking these tables together and whenever I do
something to one, it is mirrored in the others. Can find nothing that
explains this operation or how to disconnect the tables from each other.

Table structures are:

rspr=# \d "AC Power Strips"
Table "AC Power Strips"
Attribute | Type | Modifier
---------------+------------------------+----------
ID | integer |
PrintOrder | character varying(254) |
Description | character varying(254) |
FullText | text |
Image | character varying(50) |
Obsolete | character(1) |
Author | character varying(254) |
Comments | character varying(254) |
Limits | character varying(254) |
AuditRequired | character(1) |
Selections | character(1) |
SelectionList | character varying(254) |
RadioOption | character(1) |
CheckOption | character(1) |
Locked | character(1) |
fyi | character(1) |
reg | character(1) |

rspr=# \d template2
Table "template2"
Attribute | Type | Modifier
---------------+------------------------+----------
ID | integer |
PrintOrder | character varying(254) |
Description | character varying(254) |
FullText | text |
Image | character varying(50) |
Obsolete | character(1) |
Author | character varying(254) |
Comments | character varying(254) |
Limits | character varying(254) |
AuditRequired | character(1) |
Selections | character(1) |
SelectionList | character varying(254) |
RadioOption | character(1) |
CheckOption | character(1) |
Locked | character(1) |
fyi | character(1) |
reg | character(1) |

rspr=# \d "Internal Workmanship"
Table "Internal Workmanship"
Attribute | Type | Modifier
---------------+------------------------+----------
ID | integer |
PrintOrder | character varying(254) |
Description | character varying(254) |
FullText | text |
Image | character varying(50) |
Obsolete | character(1) |
Author | character varying(254) |
Comments | character varying(254) |
Limits | character varying(254) |
AuditRequired | character(1) |
Selections | character(1) |
SelectionList | character varying(254) |
RadioOption | character(1) |
CheckOption | character(1) |
Locked | character(1) |
fyi | character(1) |
reg | character(1) |

Any assistance with this problem is appreciated!

TIA,
-jim

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: James Hall (#1)
Re: Duplicate Data entry problem

James Hall <James.Hall@RadioShack.com> writes:

If I run: EXPLAIN SELECT "PrintOrder" from "acpower"; I get:
NOTICE: QUERY PLAN

Result (cost-0.00..3.27 rows=28 width=12)
-> Append (cost=0.00..3.27 rows=28 width=12)
-> Seq Scan on acpower (cost=0.00..1.13 rows=13 width=12)
-> Seq Scan on template2 acpower (cost=0.00..0.00 rows=1 width=12)
-> Seq Scan on General Requirements acpower (cost=0.00..1.11
rows=11 width=12)
-> Seq Scan on workmanship acpower (cost0.00..1.03 rows=3
width=12)

Looks to me like you created those other tables as subclasses
(inheritance children) of acpower. See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ddl-inherit.html

regards, tom lane