PostgreSQL: from OID to Bytea -
we have decided move oid
s in our postgresql 9.0 database , use bytea
columns instead. i'm trying copy data 1 column other, can't figure out right query. closest i've gotten to:
update user thistable set pkcs_as_bytea = (select array_agg(mylargeobject.data) (select * pg_largeobject loid = thistable.pkcs12_as_oid order pageno) mylargeobject) thistable.pkcs12 not null
and gives me following error message:
error: column "pkcs_as_bytea" of type bytea expression of type bytea[]
what right query then?
another way doesn't require custom function use loread(lo_open(...))
combination, like:
update user set pkcs_as_bytea = loread(lo_open(pkcs12_as_oid, 262144), 1000000) thistable.pkcs12 not null
there problem code, loread
function requires second parameter maximum number of bytes read (the 1000000
parameter used above), should use big number here if data big. otherwise, content trimmed after many bytes, , won't data bytea
field.
if want convert oid text field, should use conversion function, in:
update user set pkcs_as_text = convert_from(loread(lo_open(pkcs12_as_oid, 262144), 1000000), 'utf8')
(262144
flag open mode, 40000
in hexa, means "open read-only")
Comments
Post a Comment