Search Postgresql Archives

Re: hstore to json and back again

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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@xxxxxxxxxxxxxxxxxxxxxxxx> 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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux