PostgreSQL: from OID to Bytea
We have decided to move from OIDs in our PostgreSQL 9.0 database and use bytea columns instead. I'm trying to copy the data from one column to the other, but I can't figure out the right query. This is the closest I've gotten to:
update user as thistable set pkcs_as_bytea = (select array_agg(mylargeobject.data) from
(select * from pg_largeobject where loid = thistable.pkcs12_as_oid order by pageno) as mylargeobject) where thistable.pkcs12 is not null
And that gives me the following error message:
ERROR: column "pkcs_as_bytea" is of type bytea but expression is of type bytea[]
What would be the right query then?
Answer
Another way which doesn't require a custom function is to use the loread(lo_open(...)) combination, like:
UPDATE user SET pkcs_as_bytea = loread(lo_open(pkcs12_as_oid, 262144), 1000000) WHERE thistable.pkcs12 IS NOT NULL
There is a problem with this code, the loread function requires as the second parameter the maximum number of bytes to read (the 1000000 parameter I used above), so you should use a really big number here if your data is big. Otherwise, the content will be trimmed after this many bytes, and you won't get all the data back into the bytea field.
If you want to convert from OID to a text field, you should also use a conversion function, as in:
UPDATE user SET pkcs_as_text = convert_from(loread(lo_open(pkcs12_as_oid, 262144), 1000000), 'UTF8')
(262144 is a flag for the open mode, 40000 in hexa, which means "open read-only")