PostgreSQL NOT IN statement with Array Variable

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

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 != ANY( {{ internalOrgIds.value }} ) should work for you.


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