Duplicate Data entry problem

From: James Hall <James(dot)Hall(at)RadioShack(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Duplicate Data entry problem
Date: 2003-04-09 15:08:23
Message-ID: EE80A67DD80E304DA779C72BC9FA99960541DAA7@ntmailo.dhcp.tandy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2003-04-09 15:09:32 Re: Live Link to Oracle?
Previous Message Richard Huxton 2003-04-09 15:00:20 Re: multiple fields index