I am having quite a struggle with the presented postgres request.

I have a table objects with a few columns, including an id column. I have a table object_couples that references couples of objects with id. This table contains in consequence 2 columns of ids.

I have an external variable, like int external_variable = 42.

I am trying to select every entry of the objects table where the id of the selected object and the id of the external_variable does not exist as a couple in the object_couples table.

My request looks like the following :

SELECT id, c1, c2 FROM objects WHERE condition1 AND condition2
EXCEPT SELECT left_id, right_id FROM object_couples WHERE objects.id != object_couples.left_id AND external_variable != object_couples.right_id;

What can I do?

EDIT 1 :The following request is not rejected but causes in pycharm a code 137(SIGKILL) :

SELECT id, c1, c2 FROM objects AS S
INNER JOIN object_couples ON object_couples.left_id != S.id AND object_couples.right_id != external_variable
WHERE S.c1 > 1234 AND S.c2 < 5678

1

1 Answer

I am thinking not exists:

select o.*
from objects o
where not exists (select 1 from object_couples oc where (oc.id = oc.left and 42 = oc.right) or (oc.id = oc.right and 42 = oc.left) );

For performance, you might find that this works better:

select o.*
from objects o
where not exists (select 1 from object_couples oc where oc.id = oc.left and 42 = oc.right ) and not exists (select 1 from object_couples oc where oc.id = oc.right and 42 = oc.left );

In particular if you have indexes on object_couples(left, right) and object_couples(right, left) then this might even be fast.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.