Logical operator ignored in Postgres command

Question

I have a left join command that also checks for matching letters and two other combinctions in the data between the joined tables. The DISTINCT ON is to ensure the location_plus has no duplication as results from the table on the right. To test my results I added an AND to check for a particular account numbers results. This AND component seems to be completely ignored, returning almost all of the records instead of the ~10 for that a/c number. Why is this?

SELECT
DISTINCT ON (location_plus)
a.*,b.*
FROM schema.source_table b
LEFT JOIN schema.other_reference a
ON a.loc_id = (substring(b.loc_id,3))::integer
WHERE left(b.loc_id,1)=left(a.table_name,1)
OR ((left(b.loc_id,1)='B' AND left(a.table_name,1)='A') OR (left(b.loc_id,1)='B' AND left(a.table_name,1)='B'))
and b.account_number='12345678'

Because you are mixing OR with AND without proper parentheses. — HoneyBadger
– HoneyBadger, Commented Mar 5, 2018 at 10:45

Thorsten Kettner · Accepted Answer · 2018-03-05 10:54:51Z

1

AND has precendence over OR. So

WHERE left(b.loc_id,1)=left(a.table_name,1)
OR (...) 
and b.account_number='12345678'

is

WHERE left(b.loc_id,1)=left(a.table_name,1)
OR ((...) and b.account_number='12345678')

where you probably want it to be

WHERE (left(b.loc_id,1)=left(a.table_name,1) OR (...))
and b.account_number='12345678'

Use parentheses as shown to fix this.

answered Mar 5, 2018 at 10:54

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

2 Comments

DomRow Over a year ago

So the last part of my code nullified the entire "and/or" portion of the command because the I broke the chain?

Thorsten Kettner Over a year ago

Well, you thought it would add a condition to the whole WHERE clause, but it only added a condition to the part after OR, because without parentheses the ANDs have precedence over the ORs. So the additional condition didn't "nullify" the whole WHERE clause; only it didn't affect the result rows you got with the part before OR.

Collectives™ on Stack Overflow

Logical operator ignored in Postgres command

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