Lately I’ve had a small need to store an array of string values in a Postgres array instead of making foreign tables and dealing with the fun ActiveRecord fancies there are to play with.
Being the simpleton that I am, I found a ton of knowledge on how to query them, how to add them to your models, but still lacked some basic knowledge of how to USE them. Maybe you’re like me, and this will help.
Adding to Stuart’s code, lets assume we’ve added the postgres column:
1 2 3 4 5
Using Ruby Scopes and the proper Postgres querying syntax:
1 2 3 4
Simple enough and easy to read. Lets tackle the simple task of adding a company with a few properties: Name, website, and technology_used.
1 2 3 4 5 6 7
This will add our basic info, as well as get us our data back by doing this:
1 2 3 4 5 6 7 8
Cool, so saving the array data is easy. What about updating? As the relatabase post points out,
One huge caveat of this approach is that rails doesn’t clone the array when saving the original for dirty checking, so any in-place modification to the array won’t register that it’s changed.
This leaves us with these options:
- set the dirty flag ourselves:
- update using update_attributes:
rails_company.update_attributes(:technology_used => ['Rails', 'Redis', 'Go', 'Erlang'] )
The way Rails handles arrays is not as complete as a like, but hey, we are skipping a step by getting around foreign key tables. It’s not like you should expect much. I’d say they are allowed for small use projects, but if you really need to do some searching or have referential integrity, stick with foreign key tables.
Also, check out the following resources: