# Rails and Postgres ARRAYs

**URL:** <https://www.ruby-forum.com/t/rails-and-postgres-arrays/49474>\
**Category:** Rails\
**Created:** [December 5, 2005, 5:08am UTC](https://www.ruby-forum.com/t/rails-and-postgres-arrays/49474 "2005-12-05T05:08:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![monch1962](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee59a6/32.png) [@monch1962](https://www.ruby-forum.com/u/monch1962)\
**Post date:** [December 5, 2005, 5:08am UTC](https://www.ruby-forum.com/t/rails-and-postgres-arrays/49474/1 "2005-12-05T05:08:02Z")

</div>

Hello list,

I’ve got a Postgres table defined as  
create table A (  
id serial primary key,  
names varchar(10)[],  
val float[]  
)

Although I _could_ split out the names/val arrays into one or more  
separate tables with one-to-many relations back to table A, the actual  
business problem is such that I’d prefer not to do so if I can avoid  
it.

Is there any way of reading/writing individual items in the names &  
val fields from Rails? I’ve tried a few ideas, but haven’t come up  
with anything that works yet. Using pgsql, I can do  
INSERT INTO a (names, vals) VALUES (’{ {‘Fred’, ‘Bob’}, {1.3, 2.1} }’ );  
or  
INSERT INTO a (names, vals) VALUES (ARRAY[[‘Fred, Bob]’, [1.3, 2.1] ]);  
and  
SELECT names, values FROM A; \*\* Returns both entire arrays  
SELECT names[1], values[3] FROM A; \*\* Returns a specific element  
from each array

Has anyone used Postgres arrays with Rails (successfully)?

Regards

Dave M.

---

<div class="post-metadata">

**Author:** ![monch1962](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee59a6/32.png) [@monch1962](https://www.ruby-forum.com/u/monch1962)\
**Post date:** [December 6, 2005, 3:18am UTC](https://www.ruby-forum.com/t/rails-and-postgres-arrays/49474/2 "2005-12-06T03:18:50Z")

</div>

monch1962 wrote:

> Hello list,
> 
> I’ve got a Postgres table defined as  
> create table A (  
> id serial primary key,  
> names varchar(10)[],  
> val float[]  
> )
> 
> Although I _could_ split out the names/val arrays into one or more  
> separate tables with one-to-many relations back to table A, the actual  
> business problem is such that I’d prefer not to do so if I can avoid  
> it.
> 
> Is there any way of reading/writing individual items in the names &  
> val fields from Rails? I’ve tried a few ideas, but haven’t come up  
> with anything that works yet. Using pgsql, I can do  
> INSERT INTO a (names, vals) VALUES (’{ {‘Fred’, ‘Bob’}, {1.3, 2.1} }’ );  
> or  
> INSERT INTO a (names, vals) VALUES (ARRAY[[‘Fred, Bob]’, [1.3, 2.1] ]);  
> and  
> SELECT names, values FROM A; \*\* Returns both entire arrays  
> SELECT names[1], values[3] FROM A; \*\* Returns a specific element  
> from each array

For the selects, you could use find\_by\_sql like so (in your model):

def get\_nth\_name( n )  
return self.find\_by\_sql( “[SELECT names[?] FROM A;”, n ] )  
end

Of course, this would make your code Postgre specific, but I guess  
you’re willing to do that anyway. As for inserts, you could override  
the ActiveRecord::Base method(s) pertaining to them.

This is not guaranteed to be an optimal solution as I’m not a Rails  
expert by any means, but it should work.