Rails, PostgreSQL and... an empty array!
Hey, this will be my first post in the blog! 👨🏻💻
So... Yesterday, I came across an interesting issue or bug... since it's so old and not yet fixed in the latest Rails version.
So, the case is the following:
- Rails app with PostgreSQL database
- Checking if a value is within an database object's column of type Array
- I have a database record of type
namefield equals to Sarah
- I have the following array with names:
namesArr = ["Helen", "Peter", "Ivan"]
I want to find all users with names not within this array get them like this:
User.where('name NOT IN (?)', namesArr)
Everything works fine! But when the nameArr is empty:
 ... The interesting things happen!
The empty array becomes
NULL!!! And the SQL query is the following:
SELECT "users".* FROM "users" WHERE (name NOT IN (NULL))
And there isn't a fix yet, only workarounds!
It's really silly but it works... I just check if the array is empty and add an empty string:
if nameArr.empty? nameArr.push("") end
Hope that this solution will help you as well! 🙂