Postgresql sorts hash after saving him

Question

When I save a hash table, I want to have a specific order (as in the recording). But postgres sort it their own way. How can I specify sorting for recording?

Example:

INSERT INTO "items" ("nested_params") VALUES ($1)  [["nested_params", "\"Turbo\"=>\"Some variable\",\"TV\"=>\"Some variable\",\"FM\"=>\"Some variable\"];
SELECT  "items".* FROM "items"  WHERE "items"."id" = $2 LIMIT 1 ["id", 5]];

Output (ruby hash):

{"FM"=>"Some variable", "TV"=>"Some variable", "Turbo"=>"Some variable"}

In recording Turbo - 1st key.

P.S. I use non-english keys in hash. But they not sorted by number letters in UTF-8 encoding, and sorted randomly

Answer 1

Neither hstore nor JSON are inherently ordered. From the fine hstore manual:

The order of the pairs is not significant (and may not be reproduced on output).

and from the fine JSON specification:

An object is an unordered set of name/value pairs.

So neither one is guaranteed to care about order and the database is free to rearrange them as it sees fit. Just because the JSON version appears to maintain order doesn't mean that it will next time or will after an upgrade so don't depend on this non-specified behavior.

If you care about the order in your Hash, you'll have to do it yourself in Ruby. An easy way to do this is to override the standard ActiveRecord accessor method:

def nested_params
  super.sort_by(&:first).to_h
end

If your version of Ruby doesn't have Array#to_h then you can say:

def nested_params
  Hash[super.sort_by(&:first)]
end

You can use the hstore or json column types with those overrides. If you want the sorting to be case-insensitive then you'll have to adjust the sorting accordingly.