Thread: Is it possible to connect to another database
Hi, I try to find how is it possible to connect 2 databases, with a symbolic link. I have to use tables in another database to test user or other information. Ben
Take a look at dblink in the contrib directory... This may do what you need adam > Hi, > > I try to find how is it possible to connect 2 databases, with a symbolic > link. > > I have to use tables in another database to test user or other information. > > > Ben > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
$db_conn1 = pg_connect("dbname=db1");
$db_conn2 = pg_connect("dbname=db2");
.....
You can't join two tables from different databases(, as far as i know :).
----- Original Message -----
From: "BenLaKnet" <benlaknet@icqmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 17, 2003 5:03 PM
Subject: [SQL] Is it possible to connect to another database
> Hi,
>
> I try to find how is it possible to connect 2 databases, with a symbolic
> link.
>
> I have to use tables in another database to test user or other
information.
>
>
> Ben
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
Dear All, I am having some confusion over a query which is supposed to achieve the following: To remove a record from a table if the one or both of the columns containing references to other tables no longer point to table rows which still exist. There are good reasons why I cannot use foreign keys to maintain referential integrity, but I will not go into them, but they give rise to the need to "clean-up" my database table from time to time. The query that I have had most success with looks like this: DELETE FROM myTable WHERE (NOT myTable.item_id = item.item_id) AND (NOT myTable.group_id = ep.group_id); Which is odd, because logically it shouldn't work. What I find with the above queries is that as follows: let myTable.item_id = item.item_id be A let myTable.group_id = ep.group_id be B The derived and actual truth tables for the results of the where clause follow: Derived: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 0 0 | 0 | 1 Actual: A | B | Result 1 | 1 | 0 1 | 0 | 0 0 | 1 | 1 0 | 0 | 1 This makes no sense to me, as effectively rows 2 and 3 of the Actual results truth table are the same (unless there's some subtle difference with regards to the order of the statements, otherwise just substitute A for B and vice versa). The result that I actually want from the operation is this: A | B | Result 1 | 1 | 0 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 which would suggest a query like: DELETE FROM myTable WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = ep.group_id); which ought to provide the above output. Instead, using this query, the output I get is as follows: A | B | Result 1 | 1 | 1 1 | 0 | 1 0 | 1 | 1 0 | 0 | 1 I can only conclude that Postgres is doing something with regards to the other two tables which I am unaware of. Can anyone help me understand what is going on? Any suggestions gratefully received. Cheers Richard Richard Jones ----------------------- Systems Developer Theses Alive! - www.thesesalive.ac.uk Edinburgh University Library r.d.jones@ed.ac.uk 0131 651 1611
----- Original Message ----- From: "Richard Jones" <r.d.jones@ed.ac.uk> To: <pgsql-sql@postgresql.org> Sent: Thursday, July 17, 2003 5:29 PM Subject: [SQL] NOT and AND problem > Dear All, > > I am having some confusion over a query which is supposed to achieve the > following: To remove a record from a table if the one or both of the > columns containing references to other tables no longer point to table rows > which still exist. There are good reasons why I cannot use foreign keys to > maintain referential integrity, but I will not go into them, but they give > rise to the need to "clean-up" my database table from time to time. The > query that I have had most success with looks like this: > > DELETE FROM myTable > WHERE (NOT myTable.item_id = item.item_id) > AND (NOT myTable.group_id = ep.group_id); > > Which is odd, because logically it shouldn't work. What I find with the > above queries is that as follows: > > let myTable.item_id = item.item_id be A > let myTable.group_id = ep.group_id be B > > The derived and actual truth tables for the results of the where clause > follow: > > Derived: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 0 > 0 | 0 | 1 > > Actual: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 1 > 0 | 0 | 1 > > This makes no sense to me, as effectively rows 2 and 3 of the Actual results > truth table are the same (unless there's some subtle difference with regards > to the order of the statements, otherwise just substitute A for B and vice > versa). > > The result that I actually want from the operation is this: > > A | B | Result > 1 | 1 | 0 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > which would suggest a query like: > > DELETE FROM myTable > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > ep.group_id); If u want this u can obtain by DELETE FROM myTable WHERE (NOT(myTable.item_id = item.item_id )) OR (NOT(myTable.group_id = ep.group_id)); You can write ! instead of NOT. Look at the operations precedence. The NOT might get executed before "=". > > which ought to provide the above output. Instead, using this query, the > output I get is as follows: > > A | B | Result > 1 | 1 | 1 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > I can only conclude that Postgres is doing something with regards to the > other two tables which I am unaware of. Can anyone help me understand what > is going on? Any suggestions gratefully received. > > Cheers > > Richard > > > Richard Jones > ----------------------- > Systems Developer > Theses Alive! - www.thesesalive.ac.uk > Edinburgh University Library > r.d.jones@ed.ac.uk > 0131 651 1611 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
DELETE FROM myTable WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Jones > Sent: Thursday, July 17, 2003 10:29 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] NOT and AND problem > > > Dear All, > > I am having some confusion over a query which is supposed to > achieve the > following: To remove a record from a table if the one or both of the > columns containing references to other tables no longer point > to table rows > which still exist. There are good reasons why I cannot use > foreign keys to > maintain referential integrity, but I will not go into them, > but they give > rise to the need to "clean-up" my database table from time to > time. The > query that I have had most success with looks like this: > > DELETE FROM myTable > WHERE (NOT myTable.item_id = item.item_id) > AND (NOT myTable.group_id = ep.group_id); > > Which is odd, because logically it shouldn't work. What I > find with the > above queries is that as follows: > > let myTable.item_id = item.item_id be A > let myTable.group_id = ep.group_id be B > > The derived and actual truth tables for the results of the > where clause > follow: > > Derived: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 0 > 0 | 0 | 1 > > Actual: > A | B | Result > 1 | 1 | 0 > 1 | 0 | 0 > 0 | 1 | 1 > 0 | 0 | 1 > > This makes no sense to me, as effectively rows 2 and 3 of the > Actual results > truth table are the same (unless there's some subtle > difference with regards > to the order of the statements, otherwise just substitute A > for B and vice > versa). > > The result that I actually want from the operation is this: > > A | B | Result > 1 | 1 | 0 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > which would suggest a query like: > > DELETE FROM myTable > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > ep.group_id); > > which ought to provide the above output. Instead, using this > query, the > output I get is as follows: > > A | B | Result > 1 | 1 | 1 > 1 | 0 | 1 > 0 | 1 | 1 > 0 | 0 | 1 > > I can only conclude that Postgres is doing something with > regards to the > other two tables which I am unaware of. Can anyone help me > understand what > is going on? Any suggestions gratefully received. > > Cheers > > Richard > > > Richard Jones > ----------------------- > Systems Developer > Theses Alive! - www.thesesalive.ac.uk > Edinburgh University Library > r.d.jones@ed.ac.uk > 0131 651 1611 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
I can't help you explain what is going on with this query - like you, I am puzzled by the fact that it actually works, and have no idea how it is interpreted, and what it is doing... The right way to do what you want, I think, would be something like: delete from mytable where not exists (select 1 from item where item_id = mytable.item_id) or not exists (select 1 from ep where group_id=mytable.group_id); I replaced your AND with OR, because that's what you seem to be saying in the description of your problem... I hope, it helps.. Dima Richard Jones wrote: >Dear All, > >I am having some confusion over a query which is supposed to achieve the >following: To remove a record from a table if the one or both of the >columns containing references to other tables no longer point to table rows >which still exist. There are good reasons why I cannot use foreign keys to >maintain referential integrity, but I will not go into them, but they give >rise to the need to "clean-up" my database table from time to time. The >query that I have had most success with looks like this: > >DELETE FROM myTable >WHERE (NOT myTable.item_id = item.item_id) >AND (NOT myTable.group_id = ep.group_id); > >Which is odd, because logically it shouldn't work. What I find with the >above queries is that as follows: > >let myTable.item_id = item.item_id be A >let myTable.group_id = ep.group_id be B > >The derived and actual truth tables for the results of the where clause >follow: > >Derived: >A | B | Result >1 | 1 | 0 >1 | 0 | 0 >0 | 1 | 0 >0 | 0 | 1 > >Actual: >A | B | Result >1 | 1 | 0 >1 | 0 | 0 >0 | 1 | 1 >0 | 0 | 1 > >This makes no sense to me, as effectively rows 2 and 3 of the Actual results >truth table are the same (unless there's some subtle difference with regards >to the order of the statements, otherwise just substitute A for B and vice >versa). > >The result that I actually want from the operation is this: > >A | B | Result >1 | 1 | 0 >1 | 0 | 1 >0 | 1 | 1 >0 | 0 | 1 > >which would suggest a query like: > >DELETE FROM myTable >WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = >ep.group_id); > >which ought to provide the above output. Instead, using this query, the >output I get is as follows: > >A | B | Result >1 | 1 | 1 >1 | 0 | 1 >0 | 1 | 1 >0 | 0 | 1 > >I can only conclude that Postgres is doing something with regards to the >other two tables which I am unaware of. Can anyone help me understand what >is going on? Any suggestions gratefully received. > >Cheers > >Richard > > >Richard Jones >----------------------- >Systems Developer >Theses Alive! - www.thesesalive.ac.uk >Edinburgh University Library >r.d.jones@ed.ac.uk >0131 651 1611 > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
And after reading Viorel's response I realized that you wanted the record deleted if EITHER "foreign key" was broken, not just if BOTH fkeys are broken, therefore simply change the AND to an OR: DELETE FROM myTable WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) OR NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com Fax: (416) 441-9085 > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of > terry@ashtonwoodshomes.com > Sent: Thursday, July 17, 2003 10:29 AM > To: 'Richard Jones'; pgsql-sql@postgresql.org > Subject: Re: [SQL] NOT and AND problem > > > DELETE FROM myTable > WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id) > AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id); > > Terry Fielder > Manager Software Development and Deployment > Great Gulf Homes / Ashton Woods Homes > terry@greatgulfhomes.com > Fax: (416) 441-9085 > > > > -----Original Message----- > > From: pgsql-sql-owner@postgresql.org > > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Jones > > Sent: Thursday, July 17, 2003 10:29 AM > > To: pgsql-sql@postgresql.org > > Subject: [SQL] NOT and AND problem > > > > > > Dear All, > > > > I am having some confusion over a query which is supposed to > > achieve the > > following: To remove a record from a table if the one or > both of the > > columns containing references to other tables no longer point > > to table rows > > which still exist. There are good reasons why I cannot use > > foreign keys to > > maintain referential integrity, but I will not go into them, > > but they give > > rise to the need to "clean-up" my database table from time to > > time. The > > query that I have had most success with looks like this: > > > > DELETE FROM myTable > > WHERE (NOT myTable.item_id = item.item_id) > > AND (NOT myTable.group_id = ep.group_id); > > > > Which is odd, because logically it shouldn't work. What I > > find with the > > above queries is that as follows: > > > > let myTable.item_id = item.item_id be A > > let myTable.group_id = ep.group_id be B > > > > The derived and actual truth tables for the results of the > > where clause > > follow: > > > > Derived: > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 0 > > 0 | 1 | 0 > > 0 | 0 | 1 > > > > Actual: > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 0 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > This makes no sense to me, as effectively rows 2 and 3 of the > > Actual results > > truth table are the same (unless there's some subtle > > difference with regards > > to the order of the statements, otherwise just substitute A > > for B and vice > > versa). > > > > The result that I actually want from the operation is this: > > > > A | B | Result > > 1 | 1 | 0 > > 1 | 0 | 1 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > which would suggest a query like: > > > > DELETE FROM myTable > > WHERE NOT (myTable.item_id = item.item_id AND myTable.group_id = > > ep.group_id); > > > > which ought to provide the above output. Instead, using this > > query, the > > output I get is as follows: > > > > A | B | Result > > 1 | 1 | 1 > > 1 | 0 | 1 > > 0 | 1 | 1 > > 0 | 0 | 1 > > > > I can only conclude that Postgres is doing something with > > regards to the > > other two tables which I am unaware of. Can anyone help me > > understand what > > is going on? Any suggestions gratefully received. > > > > Cheers > > > > Richard > > > > > > Richard Jones > > ----------------------- > > Systems Developer > > Theses Alive! - www.thesesalive.ac.uk > > Edinburgh University Library > > r.d.jones@ed.ac.uk > > 0131 651 1611 > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to > > majordomo@postgresql.org) > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Hello, Just to say thanks for such fast responses. The full working query is indeed as suggested (I cut the demo query down when I posted it, as it's got some awfully long names in it in real life): DELETE FROM eulepersongroup2workspaceitem WHERE NOT EXISTS (SELECT 1 FROM workspaceitem WHERE workspace_item_id = eulepersongroup2workspaceitem.workspace_item_id ) OR NOT EXISTS (SELECT 1 FROM epersongroup WHERE eperson_group_id = eulepersongroup2workspaceitem.eperson_group_id ); Thanks very much for your help. Regards Richard Richard Jones ----------------------- Systems Developer Theses Alive! - www.thesesalive.ac.uk Edinburgh University Library r.d.jones@ed.ac.uk 0131 651 1611