Upgrade Hstore from postgresql 8.4 to 9.3

upgrade-hstore.sql

/*
 * upgrade hstore for the table: "shop_payment_methods" and it's column "data"
 * 
 * how does it work?
 *
 * 1. create a new column data_text (type text)
 * 2. set content of data::text -> data_text
 * 3. drop data
 * 4. remove hstore (classic)
 * 5. create hstore extension
 * 5. add new column data (type hstore)
 * 6. set contents of data_text::hstore -> data
 *
 */

alter table shop_payment_methods add COLUMN data_text text;
update shop_payment_methods set data_text = data::text;
alter table shop_payment_methods drop column data;

-- begin uninstall_hstore.sql (from 8.4 contrib)

SET search_path = public;

DROP OPERATOR CLASS gist_hstore_ops USING gist CASCADE;
DROP OPERATOR CLASS gin_hstore_ops USING gin CASCADE;

DROP OPERATOR ? ( hstore, text );
DROP OPERATOR ->( hstore, text );
DROP OPERATOR ||( hstore, hstore );
DROP OPERATOR @>( hstore, hstore );
DROP OPERATOR <@( hstore, hstore );
DROP OPERATOR @( hstore, hstore );
DROP OPERATOR ~( hstore, hstore );
DROP OPERATOR =>( text, text );


DROP FUNCTION fetchval(hstore,text);
DROP FUNCTION isexists(hstore,text);
DROP FUNCTION exist(hstore,text);
DROP FUNCTION isdefined(hstore,text);
DROP FUNCTION defined(hstore,text);
DROP FUNCTION delete(hstore,text);
DROP FUNCTION hs_concat(hstore,hstore);
DROP FUNCTION hs_contains(hstore,hstore);
DROP FUNCTION hs_contained(hstore,hstore);
DROP FUNCTION tconvert(text,text);
DROP FUNCTION hstore(text,text);
DROP FUNCTION akeys(hstore);
DROP FUNCTION avals(hstore);
DROP FUNCTION skeys(hstore);
DROP FUNCTION svals(hstore);
DROP FUNCTION each(hstore);
DROP FUNCTION ghstore_compress(internal);
DROP FUNCTION ghstore_decompress(internal);
DROP FUNCTION ghstore_penalty(internal,internal,internal);
DROP FUNCTION ghstore_picksplit(internal, internal);
DROP FUNCTION ghstore_union(internal, internal);
DROP FUNCTION ghstore_same(internal, internal, internal);
DROP FUNCTION ghstore_consistent(internal,internal,int,oid,internal);
DROP FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal);
DROP FUNCTION gin_extract_hstore(internal, internal);
DROP FUNCTION gin_extract_hstore_query(internal, internal, smallint, internal, internal);

DROP TYPE hstore CASCADE;
DROP TYPE ghstore CASCADE;

-- end uninstall_hstore.sql (from 8.4 contrib)

CREATE extension hstore;
ALTER table shop_payment_methods ADD COLUMN data hstore;
UPDATE shop_payment_methods SET data = data_text::hstore;