Thread: Bytea to Text problems
I've searched high and low, and all I've found is people being chided for trying to convert from Bytea to text :) When I first designed my database, I simply didn't understand the purpose of bytea, I didn't actually realize that there *was* a text data type. (Actually, I was porting from a MS SQL database, and if I remember correctly, PgAdmin actually made the decision for me) I now need to convert my field from bytea to text, but there doesn't seem to be a clean way to do it. So far I have done the following: alter table tbl_inventory ADD longdescription_new text; update tbl_inventory SET longdescription_new = encode(longdescription, 'escape'); update tbl_inventory SET longdescription_new = replace(longdescription_new, '\256', '\n') WHERE longdescription_new LIKE '%\256%'; (ad infinitum) The problem is that encode, obviously, escapes a metric ton of stuff, and I have no idea *what* is being encoded, or even what it is being encoded into, other than exhaustively digging through my data and comparing the escape codes to the original text. Is there a chart somewhere that will show me? Is there a script that will do this? John
On Tue, Sep 12, 2006 at 10:56:09AM -0500, John McCawley wrote: > I've searched high and low, and all I've found is people being chided > for trying to convert from Bytea to text :) > > When I first designed my database, I simply didn't understand the > purpose of bytea, I didn't actually realize that there *was* a text data > type. (Actually, I was porting from a MS SQL database, and if I > remember correctly, PgAdmin actually made the decision for me) I now > need to convert my field from bytea to text, but there doesn't seem to > be a clean way to do it. So far I have done the following: Doesn't straight assignment do it? Don't confuse the escaped output from bytea with the actual data. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Yeah, apparently it's OK when spewed out to the browser...Didn't think of that :P Martijn van Oosterhout wrote: >On Tue, Sep 12, 2006 at 10:56:09AM -0500, John McCawley wrote: > > >>I've searched high and low, and all I've found is people being chided >>for trying to convert from Bytea to text :) >> >>When I first designed my database, I simply didn't understand the >>purpose of bytea, I didn't actually realize that there *was* a text data >>type. (Actually, I was porting from a MS SQL database, and if I >>remember correctly, PgAdmin actually made the decision for me) I now >>need to convert my field from bytea to text, but there doesn't seem to >>be a clean way to do it. So far I have done the following: >> >> > >Doesn't straight assignment do it? > >Don't confuse the escaped output from bytea with the actual data. > >Hope this helps, > >