rubypostgresqlnosqlkyotocabinet

PostgreSQL Junction Table to a key-value store (like Kyoto Cabinet)

I am not sure what type of question this is.

What import format(s) does Kyoto Cabinet support?

For example, I have a junction table t, many-to-many relationship, in a PostgreSQL database. The size of t is about 285 million.

t =
X, Y
-----
1, a
1, b
2, a
2, c
3, c
3, d
4, b
4, e

What is the easiest, fastest way to export, then import to some key-value store like Kyoto Cabinet?

So that I end up with:

db = 
db[1] = [a,b]
db[2] = [a,c]
db[3] = [c,d]
db[4] = [b,e]

My preferred language is Ruby.

Solution

  • To get the values:

    SELECT x, array_agg(y ORDER BY y)
FROM   t
GROUP  BY x;

    Yields an array. If you want text use string_agg(y, ',' ORDER BY y).
    Probably faster:

    SELECT x, array_agg(y)
FROM  (SELECT * FROM t ORDER BY x,y) t
GROUP  BY x;