[SQL] Interest IN problem on 7.4
Hi, I am using pg 7.4. Pls, see this test: tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND IDS = 'SOF_9989'; UPDATE 0 tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND IDS = 'SOF_9989'; UPDATE 0 tt07=# update a_cars set dog_or_free=0 where virtualen=0 and dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; UPDATE 1 I think IN is not working correct in this case. In my case A_CARS.IDS is name and also OC.IDS_CAR_REAL. It is interest that the last update is working well and the first one not. Pls, comment this problem. regards, ivan. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Just to follow up I managed to track down these missing articles and have
updated the links on the website.
Robert Treat
On Thursday 04 December 2003 12:52, Clint Stotesbery wrote:
> Hi Christoph,
> Thanks for the links but the techdoc links for converting from Oracle to
> PostgreSQL has 2 links that don't go to their intended targets anymore, one
> is in the 7.3 docs which is really limited (only covers simple things), and
> the Ora2Pg one I don't really get that well.
>
> As far as updateable views, that's why you need instead of triggers.
> Regular triggers can't be done on views. So if I make an instead of trigger
> on a view that's for updates then I have an updateable view. I figured it
> out last night and I was along the right track in my original post with
> using an instead of rule to call a function. The trick is that I have to
> pass in all the old.col and new.col stuff into the function that I call
> from the rule. In Oracle since the instead of stuff is a trigger I had
> access to the new.col and old.col stuff. To do it in PostgreSQL rules I had
> to pass it all in. I'm going to write a doc about the problems I've
> encountered during my conversion project and then submit it to the Postgres
> people I think (to who though?). My programming and tech writing background
> should help I hope. Thanks for the suggestions Christoph.
> -Clint
>
>
> Original Message Follows
> From: Christoph Haller <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> CC: [EMAIL PROTECTED]
> Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
> Date: Thu, 04 Dec 2003 17:16:32 MET
>
> Not sure if this is of any help ...
> AFAIK there are no updatable views in pg.
> But aside from that, I cannot see nothing what could not be
> done by a pg trigger function:
> CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
> ON table FOR EACH { ROW | STATEMENT }
> EXECUTE PROCEDURE func ( arguments )
>
> Also try
>
> http://techdocs.postgresql.org/#convertfrom
>
> Converting from other Databases to PostgreSQL
>
> and/or
>
> http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search
>
> HTH
>
> Regards, Christoph
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
> _
> Get holiday tips for festive fun.
> http://special.msn.com/network/happyholidays.armx
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Interest IN problem on 7.4
On Sat, 13 Dec 2003, pginfo wrote: > Hi, > > I am using pg 7.4. > > Pls, see this test: > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND > IDS = 'SOF_9989'; > UPDATE 0 > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti > _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) > AND IDS = 'SOF_9989'; > UPDATE 0 > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND > OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; > UPDATE 1 > > I think IN is not working correct in this case. A NOT IN (subselect) when the subselect contains a NULL cannot ever return true by specification. -- A NOT IN (subselect) -> NOT (A IN (subselect)) NOT (A IN (subselect)) -> NOT (A = ANY (subselect)) The result of A = ANY (subselect) is derived by the application of the implied comparison predicate, R = RT for every row RT in the subselect. If the implied comparison predicate is true for at least one row RT then A = ANY (subselect) is true. If the subselect is empty or the implied predicate is false for every row RT then A = ANY (subselect) is false. Otherwise it is unknown. For the one element row RT, A = RT where RT is a NULL returns unknown. Therefore, we know that it's not an empty subselect (it returns at least one row containing NULL -- that's our precondition), and that it does not return false for every row, so A = ANY (subselect) is either true or unknown depending on whether there's another row which does match, so NOT(A = ANY(subselect)) is either false or unknown. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] INHERITS and Foreign keys
Hi
I have some problem with INHERITS and foreign keys. I do not know if I have
not got the clue or not. Anyway I have tried to simplify the problem so
that you can guide me on the right track.
I have two slightly different object A and B where 95% is common both of
data and operations. Among those are the primary key. I then put the common
stuff into P and let A and B inherit from P. It seems to work fine and
behaves like I assumed, except for foreign keys. I have a different object
L that links to P through P's PRIMARY KEY. But when I try do make an insert
it fails with foreign key constraint failure.
Below is a compressed code example illustrating the problem. If anyone can
tell me if I am trying the impossible, it is a bug, a todo, a never do, or
a patch, I would be very happy. I did search through the mailing lists to
see if I could find the answer. I found some articles about inheritance and
foreign keys, but I could not see the relevance to my problem.
My assumption is that everything you add to A and B will be seen in P. The
results of the selects indicates that I am correct. However the behavior of
the FOREIGN KEY indicates that that is not true. The references can see ONLY P.
I am using version postgresql 7.4.
Code:
CREATE TABLE p(id SERIAL PRIMARY KEY);
CREATE TABLE a(a char(2)) INHERITS(p);
CREATE TABLE b(b char(2)) INHERITS(p);
INSERT INTO p(id) VALUES(default);
INSERT INTO p(id) VALUES(default);
INSERT INTO a(a) VALUES('a1');
INSERT INTO a(a) VALUES('a2');
INSERT INTO b(b) VALUES('b1');
INSERT INTO b(b) VALUES('b2');
CREATE TABLE l(id SERIAL PRIMARY KEY, pRef bigint REFERENCES p(id));
INSERT INTO l(pRef) VALUES(1);
INSERT INTO l(pRef) VALUES(2);
INSERT INTO l(pRef) VALUES(3); --- This one fails ! You see it is there on
the select * from p.
psql:testInheritRef.sql:25: ERROR: insert or update on table "l" violates
foreign key constraint "$1"
DETAIL: Key (pref)=(3) is not present in table "p".
Here is result from SELECT.
SELECT * FROM p;
id
1
2
3
4
5
6
(6 rows)
SELECT * FROM ONLY p;
id
1
2
(2 rows)
SELECT * FROM a;
id | a
+
3 | a1
4 | a2
(2 rows)
SELECT * FROM b;
id | b
+
5 | b1
6 | b2
(2 rows)
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] INHERITS and Foreign keys
On Sat, 13 Dec 2003, [EMAIL PROTECTED] wrote: > Hi > > I have some problem with INHERITS and foreign keys. I do not know if I have > not got the clue or not. Anyway I have tried to simplify the problem so > that you can guide me on the right track. Foreign keys, unique and primary key constraints do not meaningfully inherit currently. At some point in the future, that's likely to change, but for now you're pretty much stuck with workarounds (for example, using a separate table to store the ids and triggers/rules on each of the tables in the hierarchy in order to keep the id table in date.) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] INHERITS and Foreign keys
At 20:55 12/13/2003, you wrote: On Sat, 13 Dec 2003, [EMAIL PROTECTED] wrote: > Hi > > I have some problem with INHERITS and foreign keys. I do not know if I have > not got the clue or not. Anyway I have tried to simplify the problem so > that you can guide me on the right track. Foreign keys, unique and primary key constraints do not meaningfully inherit currently. At some point in the future, that's likely to change, but for now you're pretty much stuck with workarounds (for example, using a separate table to store the ids and triggers/rules on each of the tables in the hierarchy in order to keep the id table in date.) Is there a time frame for this ? a patch, a month, a year, 7.5 or a beta version ? Thank you for the quick response. Henning ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
