Обсуждение: 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: > UPDATE forum_gtree SET gid2=gid::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/current/interactive/functions-string.html 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/zRsgOndlH63J86wRAn4aAJ0aY9Td3YcWwkIwdALInCXaruINcgCgoDtx FQmuBKPACLfE5YAmMTdCN4g= =OivL -----END PGP SIGNATURE-----
Have you seen this?
http://www.postgresql.org/docs/current/static/datatype-binary.html
Maybe you need some backslashes?
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Alvar Freude
Sent: Tuesday, December 02, 2003 5:07 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] 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:
> UPDATE forum_gtree SET gid2=gid::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/current/interactive/functions-string.html
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/zRsgOndlH63J86wRAn4aAJ0aY9Td3YcWwkIwdALInCXaruINcgCgoDtx
FQmuBKPACLfE5YAmMTdCN4g=
=OivL
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Has anyone any experience with PGSQL 7.x and implenting the FTC do-not-call list - which is about 50 million 10 digit N. American phone numbers? If so what structures have you used and what have you done interms of performance tweaks? Is there an equivalent to Oracle's IOT (index organized tables) in PGSQL? Thanks
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, uups, why I wrote the originating mail to pgsql-admin? Sorry! - -- Godshall Michael <Michael_Godshall@gmachs.com> wrote: > Have you seen this? > http://www.postgresql.org/docs/current/static/datatype-binary.html yes, but this only describe the bytea type, not how to cast some text. Or I missed something ;) ... > Maybe you need some backslashes? this also doesn't help: odem=> SELECT 'abc'::text::bytea; ERROR: Cannot cast type text to bytea odem=> SELECT '\000'::text::bytea; ERROR: Cannot cast type text to bytea 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/lOndlH63J86wRAp6MAKCJrV6S8b0LKF3xpC9EHrEhz+/uVgCgukpf sUZsrP3+u2Sxn0goxTGMAxY= =+bY9 -----END PGP SIGNATURE-----
Renney Thomas wrote: > Has anyone any experience with PGSQL 7.x and implenting the FTC > do-not-call list - which is about 50 million 10 digit N. American phone > numbers? If so what structures have you used and what have you done > interms of performance tweaks? Is there an equivalent to Oracle's IOT > (index organized tables) in PGSQL? We have a CLUSTER command. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
After a long battle with technology, renneyt@yahoo.com (Renney Thomas), an earthling, wrote: > Has anyone any experience with PGSQL 7.x and implenting the FTC > do-not-call list - which is about 50 million 10 digit N. American > phone numbers? If so what structures have you used and what have you > done interms of performance tweaks? Is there an equivalent to Oracle's > IOT (index organized tables) in PGSQL? There is a PostgreSQL keyword called "CLUSTER" which clusters a table according to an index. That organizes the table based on a (specified) index. All US and Canada phone numbers fit into 2^34, which is regrettably slightly larger than 2^32. It is highly unfortunate that 2^31 is only about 2.1 billion, because it would be Really Sweet to be able to express the phone numbers as 32 bit integers. Using 32 bit ints would be GREATLY efficient because that fits with common register sizes. What you might do would be to create a table like the following: create table do_not_call ( first_8_digits integer, last_digit int2 ); create index fast_index on do_not_call(first_8_digits); And you'd put the first 8 digits into the obvious field. The index would get you to the right page of the index Right Quick, and the structure will be reasonably compact. It's a useful way of thinking to try to make use of the HIGH efficiency of having a 32 bit value express most of what you need... -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/lisp.html Why do we drive on parkways and park on driveways?