PostgreSQL SELECT minus array (NOT IN)

Question

  SELECT DISTINCT ON
      (u.user_uuid, u.firstname)
      u.user_uuid,
      u.firstname,
      u.preposition,
      u.lastname,
      array_to_string_ex(ARRAY(SELECT email FROM emails WHERE user_uuid =
 u.user_uuid)) as emails,
      array_to_string_ex(ARRAY(SELECT mobilenumber FROM mobilenumbers WHERE
 user_uuid = u.user_uuid)) as
 mobilenumbers,
      array_to_string_ex(ARRAY(SELECT c.name FROM targetgroupusers AS tgu
 LEFT JOIN membercategories as mc ON
 mc.targetgroup_uuid =
 tgu.targetgroup_uuid LEFT JOIN
 categories AS c ON mc.category_uuid =
 c.category_uuid WHERE tgu.user_uuid =
 u.user_uuid)) as categories,
      array_to_string_ex(ARRAY(SELECT color FROM membercategories WHERE
 targetgroup_uuid IN(SELECT
 targetgroup_uuid FROM targetgroupusers
 WHERE user_uuid = u.user_uuid))) as
 colors  FROM
      membercategories AS mc  LEFT JOIN
      targetgroups AS tg  ON
      tg.targetgroup_uuid = mc.targetgroup_uuid  LEFT JOIN
      targetgroupusers AS tgu  ON
      tgu.targetgroup_uuid = tg.targetgroup_uuid  LEFT JOIN
      users AS u  ON
      u.user_uuid = tgu.user_uuid  LEFT JOIN
      emails AS e  ON
      e.user_uuid = u.user_uuid  LEFT JOIN
      mobilenumbers AS m  ON
      m.user_uuid = u.user_uuid  WHERE   mc.targetgroup_uuid IS NOT NULL  AND
 u.user_uuid !=
 ANY('{5b1f5ae5-b033-4b02-8bd0-3a9f7f2ccf19}'::UUID[])
 AND   isdeleted = false  AND
      mc.company_uuid = '5edcecd0-0534-11e0-81e0-0800200c9a66'
      AND (u.firstname ~* 'dir') OR (u.preposition ~* 'dir') OR
 (u.lastname ~* 'dir') OR (e.email ~*
 'dir') OR (m.mobilenumber ~* 'dir')
        ORDER BY
     u.firstname ASC
 OFFSET      0
 LIMIT      10

How comes i still get the result with user_uuid = 5b1f5ae5-b033-4b02-8bd0-3a9f7f2ccf19 ?!?

Quassnoi · Accepted Answer · 2010-12-27 17:40:13Z

2

Because AND has higher precedence than OR.

Enclose your OR clauses into parentheses:

AND ((u.firstname ~* 'dir') OR (u.preposition ~* 'dir') OR (u.lastname ~* 'dir') OR (e.email ~* 'dir') OR (m.mobilenumber ~* 'dir'))

answered Dec 27, 2010 at 17:40

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Koen Over a year ago

Oops, i am sorry, i already changed that, but still doesnt work! =(

Quassnoi Over a year ago

@Koen: Could you please post the query which "still doesn't work"? Also it would help much if you posted the records which you think shouldn't match but they do.

Collectives™ on Stack Overflow

PostgreSQL SELECT minus array (NOT IN)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related