From pio.gianvito@gmail.com Sat Jul  18 17:09:50 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id B50D6632679
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 18 Jul 2009 14:09:50 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 78782-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 18 Jul 2009 14:09:38 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f223.google.com (mail-ew0-f223.google.com
	[209.85.219.223])
	by mail.postgresql.org (Postfix) with ESMTP id 5FA9D6321A6
	for <pgsql-sql@postgresql.org>; Sat, 18 Jul 2009 14:09:39 -0300 (ADT)
Received: by ewy23 with SMTP id 23so1518307ewy.19
	for <pgsql-sql@postgresql.org>; Sat, 18 Jul 2009 10:09:38 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:from:to:subject
	:date:mime-version:content-type:x-priority:x-msmail-priority
	:x-mailer:x-mimeole;
	bh=DZ08picuQrRaW1z0pE03Ryc2VEi+fity1nB1MRZIxVg=;
	b=li6cUwts/SuE4U/vMXvxgHm4Ui+aFhR2fgUdcStWOsymOyjfkr4tgBz4UQrnqQUs6d
	Zfp79PkQtC36FMD8Wm7t0KexijAl1DeVb5RY6OU9o0TptBvl2+3sViQqou6wtIihQmMZ
	WMpilYPEfaHxNc33jFVimvuzkcTL/xLwj3BGQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:from:to:subject:date:mime-version:content-type
	:x-priority:x-msmail-priority:x-mailer:x-mimeole;
	b=iDnBRYxDR8AmYC3pueWulFGhlEMVDge3FXZ6CyJ5EN/IjuEO2PPC3iknO3q754vZnO
	M0H1rY8yJyPE0wb9+h8OgAIHAvbcL/KUqhn4XcZLI2AOaUQzFNxKMpWwxkvlljBZjJlr
	iYDErt67ZiBdHWltd0wSKX6fGdExAY+WLH8o8=
Received: by 10.216.28.198 with SMTP id g48mr687245wea.109.1247936977907;
	Sat, 18 Jul 2009 10:09:37 -0700 (PDT)
Received: from PCGianvito (net-93-144-73-243.t2.dsl.vodafone.it
	[93.144.73.243])
	by mx.google.com with ESMTPS id q9sm7083805gve.2.2009.07.18.10.09.35
	(version=SSLv3 cipher=RC4-MD5); Sat, 18 Jul 2009 10:09:36 -0700 (PDT)
Message-ID: <F5926D0F35F247FBAFC269BE450D5E7F@PCGianvito>
From: "Gianvito Pio" <pio.gianvito@gmail.com>
To: <pgsql-sql@postgresql.org>
Subject: Table inheritance problem
Date: Sat, 18 Jul 2009 19:09:31 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0027_01CA07DB.47DB5830"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Windows Mail 6.0.6002.18005
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200907/81
X-Sequence-Number: 32822

This is a multi-part message in MIME format.

------=_NextPart_000_0027_01CA07DB.47DB5830
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,
I have 3 tables: persons, operators and persons_position.

This is a semplified examples of their structures:

CREATE TABLE persons
(id varchar NOT NULL,
CONSTRAINT "PK_Persons" PRIMARY KEY(id));


CREATE TABLE operators
(id varchar NOT NULL,
CONSTRAINT "PK_Operators" PRIMARY KEY(id))
INHERITS(persons);

CREATE TABLE persons_position
(id bigserial NOT NULL,
person varchar NOT NULL);

and then there is a FOREIGN KEY CONSTRAINT from persons_position.person =
TO persons.id.

If I insert a tuple in operators...it results also in persons, but when =
I insert a tuple in persons_position, it says me I have violated the =
foreing key constraints. So it appears that the tuple really ISN'T in =
the persons table and the foreing key check fails.

How could I solve it, keeping the inheritance there?
Thanks
------=_NextPart_000_0027_01CA07DB.47DB5830
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 content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18783">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#e1e1e3>
<DIV><FONT size=3D2 face=3DArial>Hello,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>I have 3 tables: persons, operators and =

persons_position.</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>This is a semplified examples of their=20
structures:</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>CREATE TABLE persons</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>(id varchar NOT NULL,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>CONSTRAINT "PK_Persons" PRIMARY=20
KEY(id));</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>
<DIV><FONT size=3D2 face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2 face=3DArial>CREATE TABLE operators</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>(id varchar NOT NULL,</FONT></DIV>
<DIV><FONT size=3D2 face=3DArial>CONSTRAINT "PK_Operators" PRIMARY=20
KEY(id))</FONT></DIV>
<DIV>INHERITS(persons);</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE TABLE persons_position</DIV>
<DIV>(id bigserial NOT NULL,</DIV>
<DIV>person varchar NOT NULL);</DIV>
<DIV>&nbsp;</DIV>
<DIV>and then there is a FOREIGN KEY CONSTRAINT from =
persons_position.person TO=20
persons.id.</DIV>
<DIV>&nbsp;</DIV>
<DIV>If I insert a tuple in operators...it results also in persons, but =
when I=20
insert a tuple in persons_position, it says me I have violated the =
foreing key=20
constraints. So it appears that the tuple really ISN'T in the persons =
table and=20
the foreing key check fails.</DIV>
<DIV>&nbsp;</DIV>
<DIV>How could I solve it,&nbsp;keeping the inheritance there?</DIV>
<DIV>Thanks</DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_0027_01CA07DB.47DB5830--




From a.wenk@netzmeister-st-pauli.de Mon Jul  20 13:33:14 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id E7EA1633C58
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 20 Jul 2009 10:33:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 33815-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 20 Jul 2009 10:32:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from milkno.com (pfeiffer02.nmmn.net [217.114.78.33])
	by mail.postgresql.org (Postfix) with ESMTP id 123F2633BAF
	for <pgsql-sql@postgresql.org>; Mon, 20 Jul 2009 10:33:04 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by milkno.com (Postfix) with ESMTP id AD05015CC08D;
	Mon, 20 Jul 2009 15:32:54 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at milkno.com
Received: from milkno.com ([127.0.0.1])
	by localhost (localhost [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id gXDQhoMs2rvp; Mon, 20 Jul 2009 15:32:54 +0200 (CEST)
Received: from [192.168.97.30] (automatix.transfer.langbehn.hh.nmmn.net
	[217.114.64.90]) by milkno.com (Postfix) with ESMTPA id CF74A15CC08C;
	Mon, 20 Jul 2009 15:32:53 +0200 (CEST)
Message-ID: <4A64720B.1060904@netzmeister-st-pauli.de>
Date: Mon, 20 Jul 2009 15:32:59 +0200
From: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Gianvito Pio <pio.gianvito@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Table inheritance problem
References: <F5926D0F35F247FBAFC269BE450D5E7F@PCGianvito>
In-Reply-To: <F5926D0F35F247FBAFC269BE450D5E7F@PCGianvito>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL=0.003
X-Spam-Level: 
X-Archive-Number: 200907/85
X-Sequence-Number: 32826

Gianvito Pio schrieb:
> Hello,
> I have 3 tables: persons, operators and persons_position.
>  
> This is a semplified examples of their structures:
>  
> CREATE TABLE persons
> (id varchar NOT NULL,
> CONSTRAINT "PK_Persons" PRIMARY KEY(id));
>  
>  
> CREATE TABLE operators
> (id varchar NOT NULL,
> CONSTRAINT "PK_Operators" PRIMARY KEY(id))
> INHERITS(persons);
>  
> CREATE TABLE persons_position
> (id bigserial NOT NULL,
> person varchar NOT NULL);
>  
> and then there is a FOREIGN KEY CONSTRAINT from persons_position.person 
> TO persons.id.
>  
> If I insert a tuple in operators...it results also in persons, but when 
> I insert a tuple in persons_position, it says me I have violated the 
> foreing key constraints. So it appears that the tuple really ISN'T in 
> the persons table and the foreing key check fails.
>  
> How could I solve it, keeping the inheritance there?
> Thanks

Hi,

sure it does throw that error. Your foreign key constraint is wrong. The check will look 
like this:

test=# insert into persons_position values(2,'andy');
ERROR:  insert or update on table "persons_position" violates foreign key constraint 
"persons_position_persons_fkey"
DETAIL:  Key (person)=(andy) is not present in table "persons".

You could solve it by altering the table persons:

test=# alter table persons add column person varchar(255);
ALTER TABLE
test=# insert into persons_position values(2,'andy'); 

INSERT 0 1

Or change the foreign key constraint in persons_position to id (be careful - the datatypes 
are different)

Cheers

Andy