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)).*
FROMfoo
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
SETdatahash_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
p...@illuminatedcomputing.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 = x(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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
