Querying a PostgreSQL multi-dimensional array data type in Rails 4

Question

I am using a PostgreSQL multi-dimensional array to mimic an array of hashes, and I am looking for a way to locate a record by a key-value pair in that array like e.g ["key1","value1"]. An example array is:

[ ["key1","value1"], ["key2","value2"] ]

The keys are in my case languages and a key may occur more than once.

Is there an efficient way to locate a record by a key-value pair using a PostgreSQL index with Rails 4?

Edit: fixed typo

can you use extensions? then look on Hstore extension - it is exactly what you want - stackoverflow.com/questions/13766300/hstore-and-rails — Pavel Stehule
– Pavel Stehule, Commented Jun 25, 2013 at 7:34
I looked at Hstore, but as Craig says it only supports single-level keys and due to key collision I would need to return an array for a value like e.g { "key1" => ["value1", "value2"] }. — Andreas Sæbjørnsen
– Andreas Sæbjørnsen, Commented Jun 25, 2013 at 8:56
Is there any particular reason for looking for a convoluted storage solution instead of database fields and tables? — Denis de Bernardy
– Denis de Bernardy, Commented Jun 25, 2013 at 9:15
Denis: I have a table that must be auditable, and that also need slugs per locale. I am trying to create a slugging library that does not need extra tables to simplify interactions with the audit requirement, but it might be a blind alley. — Andreas Sæbjørnsen
– Andreas Sæbjørnsen, Commented Jun 25, 2013 at 9:20
@AndreasSæbjørnsen What does single-field give you that a two-level table doesn't in audit terms? Either way, you have a transaction that adds a change or set of changes to an audit table using a trigger when the table changes. — Craig Ringer
– Craig Ringer, Commented Jun 25, 2013 at 9:41

Craig Ringer · Accepted Answer · 2013-06-25 09:39:38Z

3

I am using a PostgreSQL multi-dimensional array to mimic an array of hashes

Those two things aren't really all that similar, and I wouldn't recommend attempting to use multidimensional arrays to model nested hashes.

Pavel is quite right that hstore is probably a lot closer to what you want, and it's indexable too. However, the current version of hstore (in Pg 9.3 and older) supports only single-level keys; it's a dictionary/hash that can contain only scalar string values. A planned enhancement to hstore for PostgreSQL 9.4 will hopefully bring multi-level nesting and JSON syntax compatibility.

Ordinary tables

You can model arbitrary-depth key/value chains(and trees/graphs) using edgelists and recursive CTEs, but this probably rather more complexity than you really want.

If you only need a fixed two-level key/value list, simply use a table that lists both key levels:

CREATE TABLE twolevel(key1 text, key2 text, thevalue text not null, PRIMARY KEY(key1,key2));

This lets you constrain against duplicate key pairs, which is nice.

You can also use two tables with a foreign key relationship between them. This gives you cascade deletes if you want, so removing a top level key removes all sub-level keys and associated values. It's easy enough to do that with the single-table approach, though.

Use one of these two approaches unless you have good reasons to do otherwise.

Hstore as text

Until the extended hstore is available, one option would be to store text representations of nested hstore fields. This is not pretty or efficient, but it's probably better than trying to search a multidimensional array.

CREATE TABLE nested_hstore(id integer, blah hstore);

insert into nested_hstore(id, blah) values 
(1, hstore( ARRAY['key1','key2'], ARRAY['"key1.1"=>"value1.1", "key1.2"=>"value1.2"', '"key2.1"=>"value2.1", "key2.2"=>"value2.2"']::hstore[]::text[]));

Test:

regress=> select (blah->'key1')::hstore->'key1.1' from nested_hstore ;
 ?column? 
----------
 value1.1
(1 row)

Because the hstore must be parsed each time it's not going to be super-fast, and you won't get the usual indexing benefits on the second level. Still, it's an option if you really genuinely need two-level hashes in fields.

Tables of hstore values

You can combine these two quite reasonably.

CREATE TABLE twolevel(key1 text, level2keyvalues hstore);

It seems pretty ugly to me, though; I'd prefer to be consistent one way or the other.

SQL/XML

Another option is to use SQL/XML, which you can index along arbitrary XPATH expressions. Again, this seems a bit too complicated.

edited Jun 25, 2013 at 9:39

answered Jun 25, 2013 at 8:45

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andreas Sæbjørnsen Over a year ago

It is hard for me to make an informed choice about EdgeLists and recursive CTEs since I do not have much experience with them. However, you are correct that it seems like an undesirable solution because of its complexity. Maybe I am stuck using multiple arrays, one for each language? That is also not desirable.

Craig Ringer Over a year ago

@AndreasSæbjørnsen Added some detail. Basically I think you should probably just use an ordinary table for this.

Andreas Sæbjørnsen Over a year ago

I ended up using a more traditional schema design as you suggested. It does not seem like the current implementation of the hstore and array feature is that useful in this context.

Craig Ringer Over a year ago

@AndreasSæbjørnsen I tend to agree. If the v2 hstore work that supports nesting and json syntax goes well then hopefully that'll change, maybe in 9.5...

Collectives™ on Stack Overflow

Querying a PostgreSQL multi-dimensional array data type in Rails 4

1 Answer 1

Ordinary tables

Hstore as text

Tables of hstore values

SQL/XML

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Ordinary tables

Hstore as text

Tables of hstore values

SQL/XML

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related