Обсуждение: 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?