I have a table where I have stored plain text passwords in a varchar field.
I would like to convert these to an encoded field. I have been able to
insert values into a test table using this syntax with a literal password:

insert into testtable (name, password) values ('glenn',encode
('mypassword','base64'));

and I can read them back out with the decode(password,'base64') syntax.
This is the behavior I want.

But when I try to encode data from an existing table:

create table newtable as select name, encode(password,'base64') as password
from oldtable;

I get this error

ERROR: Function encode(character varying, "unknown") does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts


I have tried the following casts

password::text
case(password as text)

but I still get the error:

ERROR: Function encode(text, "unknown") does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts

Is there a way I can accomplish this task?

Thanks.



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

http://archives.postgresql.org

Glenn_Wiens (AT) via-christi (DOT) org writes:

The problem is that there is no cast from varchar to bytea (which is
what encode takes as first argument). Try creating the cast first:

CREATE CAST (varchar as bytea) WITHOUT FUNCTION;
SELECT encode(password::bytea, 'base64'::text) FROM texttable;

You may want to drop the cast afterwards.

Regards,
Manuel.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thanks -- that did the trick! And when I went to consult my documentation
about this command, I realized I was looking at 7.2 docs (downloaded) vs my
currently running version of 7.3.





Manuel Sugawara
<masm (AT) fciencias (DOT) una To: Glenn_Wiens (AT) via-christi (DOT) org
m.mx> cc: pgsql-novice (AT) postgresql (DOT) org
Sent by: Fax to:
masm (AT) conexa (DOT) fcienci Subject: Re: [NOVICE] Problem with encode() function
as.unam.mx


05/21/2004 05:21 PM






Glenn_Wiens (AT) via-christi (DOT) org writes:

The problem is that there is no cast from varchar to bytea (which is
what encode takes as first argument). Try creating the cast first:

CREATE CAST (varchar as bytea) WITHOUT FUNCTION;
SELECT encode(password::bytea, 'base64'::text) FROM texttable;

You may want to drop the cast afterwards.

Regards,
Manuel.





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org