471,305 Members | 1,278 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,305 software developers and data experts.

Cast text to bytea

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I want to change a column from text to bytea; since it seems that alter
table can't change the column type, i have to add a temporary column and
copy the data from the old one to the new, delete the old and rename the
new.
But it seems that Postgres can't cast text to bytea:

odem=> UPDATE forum_gtree SET gid2=gid::bytea;
ERROR: Cannot cast type text to bytea
The same with casting only:

odem=> SELECT 'abc'::text::bytea;
ERROR: Cannot cast type text to bytea
odem=> SELECT '\000'::text::bytea;
ERROR: Cannot cast type text to bytea
How can I do this?

encode/decode seems to handle only bytea data:

http://www.postgresql.org/docs/curre...ns-string.html
And in http://www.postgresql.org/docs/curre...pe-binary.html I
also found no solution -- perhaps I missed something?
Thanks && Ciao
Alvar
- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zZ/hOndlH63J86wRAmvoAJ4jwJp5R0nrmf8FzG9O599ED/8eCgCeOO2L
RpqleJNIv5RKvorYzh5+lo4=
=7mYs
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #1
2 19436
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Alvar Freude <al***@a-blast.org> wrote:
But it seems that Postgres can't cast text to bytea:

odem=> UPDATE forum_gtree SET gid2=gid::bytea;
ERROR: Cannot cast type text to bytea


I tryed it with DECODE:

odem=> UPDATE forum_gtree SET gid2=(DECODE(gid::text, 'escape'));
ERROR: decode: Bad input string for type bytea

hmmm ...
The text columns contains values from \x01 to \xff -- everything exept the
null byte.
So, hmmm, it seems I'm faster writing a small piece of Perl to convert this
then searching a solution ... ;)
Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/za68OndlH63J86wRAmE/AKCCaQU/YDFNv6fnri6D7I6Q74ts7QCfcqE1
S+I44j/Lx3mots/pt5EWBJE=
=LVOG
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Alvar Freude <al***@a-blast.org> wrote:

odem=> UPDATE forum_gtree SET gid2=(DECODE(gid::text, 'escape'));
ERROR: decode: Bad input string for type bytea


the solution is: backslashes in the text column are interpreted as escapes.
So: they must be marked escaped first. Replacing "\" with "\\" does this:

UPDATE table SET bytea_col =
(decode(replace(text_col, '\\', '\\\\'), 'escape'));
This seems to work and looks like there are the correct values in the new
column ...
Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zePPOndlH63J86wRAmEaAJ9LAWvLFwekca58BTWYYBuPgWrawA CeJRjl
POrmVVWkT0+XmF7clqBtKlA=
=wH3M
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by mPath Records | last post: by
4 posts views Thread by Morten Goodwin Olsen | last post: by
1 post views Thread by Baldur Norddahl | last post: by
reply views Thread by Alvar Freude | last post: by
3 posts views Thread by btober | last post: by
7 posts views Thread by Dennis Gearon | last post: by
reply views Thread by Peter Wang | last post: by
reply views Thread by Oliver Nolden | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.