From: Richard Jones <r.d.jones@ed.ac.uk>
To: pgsql-sql@postgresql.org
Subject: NOT and AND problem
Date: Thu, 17 Jul 2003 15:29:11 +0100
Message-ID: <009f01c34c6f$ca98d3c0$5192d781@lib.ed.ac.uk> (raw)
References: <BB3C6BB2.20059%awitney@sghms.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: r.d.jones@ed.ac.uk
Subject: Re: NOT and AND problem
In-Reply-To: <009f01c34c6f$ca98d3c0$5192d781@lib.ed.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox