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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFBuB8IB7bNG8LQkwRAviOAKCCiFrFrQ2Ap6kjelBY6T YzQRIaVgCghjvY
nHBj3ky/WNwuSjlog1vZS5s=
=eReP
-----END PGP SIGNATURE-----

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,
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings