Postgres: update value of TEXT column (CLOB)

user_2468620 0
Viewed 2995

I have a column of type TEXT which is supposed to represent a CLOB value and I'm trying to update its value like this:

UPDATE my_table SET my_column = TEXT 'Text value';

Normally this column is written and read by Hibernate and I noticed that values written with Hibernate are stored as integers (perhaps some internal Postgres reference to the CLOB data).

But when I try to update the column with the above SQL, the value is stored as a string and when Hibernate tries to read it, I get the following error: Bad value for type long : ["Text value"]

I tried all the options described in this answer but the result is always the same. How do I insert/update a TEXT column using SQL?

clob postgresql hibernate
1 Answers

In order to update a cblob created by Hibernate you should use functions to handling large objects:

the documentation can be found in the following links:

https://www.postgresql.org/docs/current/lo-interfaces.html
https://www.postgresql.org/docs/current/lo-funcs.html

Examples:

To query:

select mytable.*,  convert_from(loread(lo_open(mycblobfield::int, x'40000'::int), x'40000'::int),  'UTF8') from mytable where mytable.id = 4;

Obs:
x'40000' is corresponding to read mode (INV_WRITE)

To Update:

select lowrite(lo_open(16425, x'60000'::int), convert_to('this an updated text','UTF8'));

Obs:
x'60000' = INV_WRITE + INV_READ is corresponding to read and write mode (INV_WRITE + IV_READ).
The number 16425 is an example loid (large object id) which already exists in a record in your table. It's that integer number you can see as value in the blob field created by Hinernate.

To Insert:

select lowrite(lo_open(lo_creat(-1), x'60000'::int), convert_to('this is a new text','UTF8'));

Obs:
lo_creat(-1) generate a new large object a returns its loid

user_39939180
Related