Forum: Ruby on Rails Rails and Postgres ARRAYs

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
monch1962 (Guest)
on 2005-12-05 06:08
(Received via mailing list)
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.
Jonathan <> <. (Guest)
on 2005-12-06 04:18
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.
This topic is locked and can not be replied to.