Re: hstore to json and back again

> I'm trying to migrate an existing hstore column to json in Postgres
> 9.3, and I'd like to be able to run the script in reverse.

To answer my own question, this partially solves the problem for me
(foo.datahash_new has json, foo.datahash_old has hstore):

connection.select_rows(<<-EOQ).each do |id, key, value|
SELECT id,
(json_each_text(datahash_new)).*
FROM foo
EOQ
key = connection.quote(key)
value = connection.quote(value)
connection.execute <<-EOQ
UPDATE foo
SET datahash_old = COALESCE(datahash_old, ''::hstore) ||
hstore(#{key}, #{value})
WHERE id = #{id.to_i}
EOQ
end

That is Ruby driving the SQL. So this is a SELECT and then a bunch of
UPDATEs. I'd love to convert this to a single UPDATE, but I couldn't
get that to work. I tried this:

UPDATE foo
SET datahash_old = COALESCE(datahash_old, ''::hstore) ||
hstore(x.key, x.value)
FROM (SELECT id, (json_each_text(datahash_new)).*
FROM foo) x(id, key, value)
WHERE foo.id = x.id

But that doesn't work, because multiple json key/value pairs for the
same foo.id don't accumulate---instead each one wipes out the previous
one, so the hstore column winds up with just one key/value pair.

Any suggestions for making this one big UPDATE?

Thanks,
Paul

On Tue, Jul 1, 2014 at 3:26 PM, Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
> Hello,
>
> I'm trying to migrate an existing hstore column to json in Postgres
> 9.3, and I'd like to be able to run the script in reverse. I know not
> all JSON can turn back into hstore, but since this is coming from an
> old hstore column, I know the structure is flat (no nesting), and that
> all values are strings.
>
> Here is the SQL I'm using to go hstore -> json:
>
> UPDATE foo
> SET datahash_new = to_json(datahash_old)
> ;
>
> Is there any SQL I can use to go backwards?:
> UPDATE foo
> SET datahash_old = xxxxx(datahash_new)
> ;
>
> I understand why there is not a general-purpose solution, but in my
> case this should be possible. I've tried to cook something up with
> json_each_text, but I haven't been able to figure it out. Can anyone
> offer any help?
>
> Thanks,
> Paul
>
> --
> _________________________________
> Pulchritudo splendor veritatis.

--
_________________________________
Pulchritudo splendor veritatis.