Thread: How to cast VARCHAR to BYTEA and vice-versa?
Hello all, I have a table with a VARCHAR column that I need to convert to a BYTEA. How do I cast VARCHAR to BYTEA? The following doesn't seem to work as it yields the 'cannot cast varchar to bytea' error message: varchar_data::bytea On the same topic, how do I do the reverse, that is, how to I cast from BYTEA back to VARCHAR? Thanks, -Michael Moran
On Tue, 2005-03-08 at 07:31 -0800, Moran.Michael wrote: > Hello all, > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > How do I cast VARCHAR to BYTEA? have you looked at the encode() and decode() functions ? http://www.postgresql.org/docs/7.4/interactive/functions-string.html gnari
"Moran.Michael" wrote: > > Hello all, > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > How do I cast VARCHAR to BYTEA? > > The following doesn't seem to work as it yields the 'cannot cast varchar to > bytea' error message: > > varchar_data::bytea > > On the same topic, how do I do the reverse, that is, how to I cast from > BYTEA back to VARCHAR? > > Thanks, > -Michael Moran > > As far as I remember varchar_data::TEXT::BYTEA works. Vice versa dto. Be aware BYTEA data may contain character codes unconvertable to VARCHAR, especially '\0'. Regards, Christoph
Christoph, is that recent functionality? I'm running 7.4 and I get: ERROR: cannot cast type text to bytea I get similar errors when trying to go the other direction. On Mon, 14 Mar 2005 11:32:26 +0100, Christoph Haller <ch@rodos.fzk.de> wrote: > "Moran.Michael" wrote: > > > > Hello all, > > > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > > > How do I cast VARCHAR to BYTEA? > > > > The following doesn't seem to work as it yields the 'cannot cast varchar to > > bytea' error message: > > > > varchar_data::bytea > > > > On the same topic, how do I do the reverse, that is, how to I cast from > > BYTEA back to VARCHAR? > > > > Thanks, > > -Michael Moran > > > > > As far as I remember > varchar_data::TEXT::BYTEA > works. Vice versa dto. > Be aware BYTEA data may contain character codes > unconvertable to VARCHAR, especially '\0'. > > Regards, Christoph > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Seems my remembrance was wrong, ::TEXT::BYTEA causes ERROR: cannot cast type text to bytea on my 7.4.5 version too. Looking into "Other Binary String Functions" within the manual, sheds light on how to do it: ::TEXT::BYTEA Function decode(string text, type text) Return Type bytea select decode('123\\000456'::text, 'escape'); decode ------------123\000456 (1 row) ::BYTEA::TEXT Function encode(string bytea, type text) Return Type text select encode('123\\000456'::bytea, 'escape'); encode ------------123\000456 (1 row) Sorry for the confusion I have caused, especially because there already was a correct answer sent by Ragnar. Regards, Christoph Lance Peterson wrote: > > Christoph, is that recent functionality? I'm running 7.4 and I get: > ERROR: cannot cast type text to bytea > > I get similar errors when trying to go the other direction. > > On Mon, 14 Mar 2005 11:32:26 +0100, Christoph Haller <ch@rodos.fzk.de> wrote: > > "Moran.Michael" wrote: > > > > > > Hello all, > > > > > > I have a table with a VARCHAR column that I need to convert to a BYTEA. > > > > > > How do I cast VARCHAR to BYTEA? > > > > > > The following doesn't seem to work as it yields the 'cannot cast varchar to > > > bytea' error message: > > > > > > varchar_data::bytea > > > > > > On the same topic, how do I do the reverse, that is, how to I cast from > > > BYTEA back to VARCHAR? > > > > > > Thanks, > > > -Michael Moran > > > > > > > > As far as I remember > > varchar_data::TEXT::BYTEA > > works. Vice versa dto. > > Be aware BYTEA data may contain character codes > > unconvertable to VARCHAR, especially '\0'. > > > > Regards, Christoph > >