Re: Casting bytea to varchar

Jayashree Rajagopalan wrote:
> I've to alter a column which is of datatype bytea to varchar(255).
>
> I used this:
> ALTER TABLE tablename ALTER COLUMN columname TYPE varchar(255);
>
> But I'm really not sure, if the value is casted properly.
> Is there a way to explicity to cast the value, while altering a columns.
>
> Example sql are welcome.

What you did is maybe not what you expect.
See the following example (9.2, encoding UTF8):

CREATE TABLE test (id integer primary key, val bytea);
INSERT INTO test VALUES (1, 'Schön'::bytea);
ALTER TABLE test ALTER COLUMN val TYPE varchar(255);
INSERT INTO test VALUES (2, 'Schön');
SELECT * FROM test;
id | val
----+----------------
1 | \x536368c3b66e
2 | Schön
(2 rows)

So the bytea column will be replaced with the string
representation of the bytea.

You cannot specify a conversion function while altering
a column's type, you'd have to use a new column like this:

ALTER TABLE test ADD COLUMN val2 varchar(255);
UPDATE test SET val2 = convert_from(val, 'UTF8'); -- because my encoding is UTF8
ALTER TABLE test DROP COLUMN val;
ALTER TABLE test RENAME COLUMN val2 TO val;

Yours,
Laurenz Albe