PostgreSQL: from OID to Bytea -


we have decided move oids 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

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -