PostgreSQL NOT IN statement with Array Variable

Queries and Resources
1

  • Goal: I have a complicated SQL query I want to add in a NOT IN () where are populated from a variable.

  • Details: My full query (complicated but mainly look at final part)

SELECT
  referred_org.id AS referred_org_id,
  referred_org.name AS referred_org_name,
  proj.id AS proj_id,
  proj.shopify_url AS proj_shopify_url,
  sub.id AS sub_id,
  sub.started_at AS sub_started,
  sub.ended_at AS sub_ended,
  sub.monthly_amount AS sub_monthly_amount,
  referral_code.id AS referral_code_id,
  referral_code.code AS referral_code,
  referral_code.discount_percentage AS discount_percentage,
  referral_org.id AS referral_org_id,
  referral_org.name AS referral_org_name,
  referral_org.paypal_payout_email AS referral_org_paypal_email
FROM
  account_organization referred_org
  JOIN core_subscription sub ON referred_org.id = sub.organization_id
  JOIN core_store proj ON referred_org.id = proj.owner_organization_id
  JOIN account_referralcode referral_code ON proj.referrer_key = referral_code.code
  JOIN account_organization referral_org ON referral_org.id = referral_code.organization_id
WHERE
  sub.monthly_amount > 0
  AND sub.started_at <= {{ moment(monthSelect.value, 'MMMM YYYY').endOf('month').format('YYYY-MM-DD') }}
  AND referral_org.id NOT IN ( {{ internalOrgIds.value }} )

so it's the ( {{ internalOrgIds.value }} ) that's giving me trouble. I've tried join(",") and lots of other permutations of things but it always errors out.

2

For arrays, you want to use foo = ANY( [val1, val2, etc...] ) for finding if foo is IN the array, or foo != ANY( [val1, val2, etc...] ) for finding if foo is NOT IN the array.

So AND referral_org.id != ANY( {{ internalOrgIds.value }} ) should work for you.

3 Likes
3

wow thought i tried this but i guess i got it slightly wrong. was getting strange syntax errors but this works like a charm! thanks!

1 Like