Postgresql - alter column type from oid to bytea with data retention

Question

Now I'm developing application that uses hibernate + postgresql to persist data. We need to store a number of serializable HashMaps, so LOB (oid) was used for this purpose. But due to some problems with oid deletion in postgresql we decide to store this data as bytea instead of oid. So the question is that what is the best way to alter column type from oid to bytea with retaining old data? If someone can give a working script/solution for such a data transition it will be great.

Thank you in advance.

Answer 1

i had the same issue. So i did it with four requests with creating buffered column

ALTER TABLE attachment add column content_bytea bytea
UPDATE attachment SET content_bytea = lo_get(content)
ALTER TABLE attachment drop column content
ALTER TABLE attachment rename column content_bytea to content