16-byte password hashes of some application are stored in the postgresql-9.1 database as LOB data. (I don't know why)

So I can get hash:

# select * from pg_largeobject where loid=16916;
 loid  | pageno |                data                
 16916 |      0 | \x098f6bcd4621d373cade4e832627b4f6

How can I replace data of this blob or create new blob with specified data in postgres-9.1?

I see that there is lo_from_bytea function since posgresql-9.4. But there is no something like this in 9.1.

I can use lo_import - load lob from external file, but it is hard-way. Is there something simplier?

1 Answer 1


Note that PostgreSQL version 9.1 has been obsoleted in 2016, it should no longer be used.

Anyway, there is a set of large object server-side functions in 9.1 that should cover your needs. They tend to expose a large object as if it was a file, providing file-like primitives.

For instance, here's a plpgsql function that replaces the value inside a large object using these primitives. It should work with 9.1 or newer:

CREATE FUNCTION lo_replace(_loid oid, _str bytea) RETURNS void
AS $$ 
 fd integer;
 fd = lo_open(_loid, 393216); -- INV_READ|INV_WRITE
 if (fd < 0) then
   raise exception 'Failed to open large object %', _loid;
 end if;

 if (lo_truncate(fd, 0) != 0) then
   raise exception 'Failed to truncate large object %', _loid;
 end if;

 if (lowrite(fd, _str) != octet_length(_str)) then
   raise exception 'Failed to write to large object %', _loid;
 end if;

 if (lo_close(fd) != 0) then
   raise exception 'Failed to close large object %', _loid;
 end if;
$$ LANGUAGE plpgsql; 

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.