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
1       123             55123
2       234             51614
3       345             56127
should be
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?



2 Answers 2


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.

  • Did you try loread()? postgresql.org/docs/9.0/static/lo-funcs.html
    – user1822
    Feb 3, 2015 at 15:08
  • loread does not appear to be available to me as either postgres, or the database owner. "No function matches the given name and argument types"
    – John P
    Feb 4, 2015 at 14:22
  • Perhaps it's because the correct function name is lo_read.
    – Andriy M
    May 29, 2017 at 6:03
  • Documentation for lo_get is here: postgresql.org/docs/9.6/static/lo-funcs.html
    – Freiheit
    Jun 25, 2018 at 18:33
  • I've used a little tweeked version: update answers set answer_value = convert_from(lo_get(cast(value as bigint)), 'UTF8')
    – marioosh
    Mar 28, 2023 at 12:44

Query : SELECT encode(lo_get([OID], [offset], [length]), 'escape');

You can get the actual data present at particular OID and then execute update query. e.g.,

temp_table => SELECT encode(lo_get(238026, 0, 10), 'escape');
(1 row)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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