8

I have a database table that looks like:

create table answers(
   id int not null,
   question_id int not null,
   answer text null
)

This table was originally build by Hibernate using the @Lob attribute for the "answer" column. I did not realize it at the time, but when setup that way, Hibernate stores an OID in the column instead of the actual text. Everything works fine when I use Hibernate to retrieve the values since it automatically converts the OID to the CLOB string, however it is becoming a performance problem and I'd like to get rid of the OID.

select * from answers
ID     QUESTION_ID     ANSWER
===============================
1       123             55123
2       234             51614
3       345             56127
should be
ID     QUESTION_ID     ANSWER
===============================
1       123             Male
2       234             203-555-1212
3       345             555 Main St. New York, NY

My desire is to add an extra column to the table "ANSWER_VALUE TEXT" and do something like below to get the actual value into the table, then change Hibernate around to not use the @Lob designator

update answers set ANSWER_VALUE= getValueFromOID(ANSWER)

Does that "getValueFromOID" function exist? If not, could I get some pointers on how to create one or at least how to fetch the actual value of an OID?

Thanks

10

Thanks goes to a_horse_with_no_name. Solution is:

update answers set answer_value = lo_get(cast(value as bigint))

Note - the lo_get function appears to be present in Postgres 9.4 or higher. For earlier versions, I don't see a way of doing this directly. I'm currently running 9.0, but this just accelerated my upgrade plans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.