Rails and Postgres ARRAYs

Rails
1

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.

2

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.