[GENERAL] Table Inheritance and foreign key problem.

[Kostas Maistrelis]

I make 3 tables with this senario:
b (id) extends a (id)
and
c (id) references a (id)

i insert one record to  table b (id = 1)
and then i try to insert a record in table C  (id = 1)
but it is not possible, i get error
(look at the sql statements above)


NOTE
if the C (id) references the b (id) the insert in  table C
is possible.

I try this to postgresql 8.1.2 and 8.1.4


above is the exact SQL STATMENTS:



DROP TABLE a;
DROP TABLE b;
DROP TABLE c;

CREATE TABLE  a (id integer primary key);

CREATE TABLE  b (id integer primary key) INHERITS (a);

CREATE TABLE c (id integer primary key references a(id));

insert into b values (1);





SELECT * from b;
id

 1
(1 row)


SELECT * from a;
id

 1
(1 row)


INSERT INTO c VALUES (1);
ERROR:  insert or update on table c violates foreign key constraint
c_id_fkey
DETAIL:  Key (id)=(1) is not present in table a.






Thanks

Kostas Maistrelis.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] sql question; checks if data already exists before

[Kostas Maistrelis]

nuno wrote:

hi, there. i'm trying to write a SQL statement which does the following
things.

1. checks if data already exists in the database
2. if not, insert data into database otherwise skip.

  

Check this thread :

http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] mysql replace in postgreSQL?

[Kostas Maistrelis]

blackwater dev wrote:


In MySQL, I can use the replace statement which either updates the
data there or inserts it.  Is there a comporable syntax to use in
postgreSQL?

I need to do an insert and don't want to have to worry about if the
data is already there or not...so don't want to see if it there, if so
do update if not insert...etc.
 



look this functions..
is not general solution..


CREATE TYPE mydata AS (
   f1 integer ,
   f2 integer,
);



CREATE OR REPLACE FUNCTION updatefoo(data  mydata, myid bigint) RETURNS 
boolean AS $$

DECLARE
BEGIN
   update foo_table  set
   f1 = mydata.f1,
   f2 = mydata.f2
   WHERE id  = myid;

IF NOT FOUND THEN
return false;
END IF; 
return true;

END
$$ LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION insertfoo(data  mydata, myid bigint) RETURNS 
boolean AS $$

DECLARE
   rep boolean DEFAULT false;
BEGIN
   insert into foo_table (
id , 
f1,
f2   
   ) values (

 mydata.id,
 mydata.f1,
 mydata.f2
   );

 return rep;
END
$$ LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION replaceFoo(data  mydata, myid bigint) RETURNS 
boolean AS $$

DECLARE
rep boolean = false;
BEGIN
   rep = updatefoo(mydata,myid );
   if not rep then
   rep =  insertfoo(mydata,myid );
   end if;
   return rep;
END
$$ LANGUAGE plpgsql;


---(end of broadcast)---
TIP 6: explain analyze is your friend