Thread: 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

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> You cannot specify a conversion function while altering
> a column's type, you'd have to use a new column like this:

Sure you can; that's the whole point of the USING option.
It'd look something like

ALTER TABLE test ALTER COLUMN val TYPE varchar(255) USING convert(val);

with whatever is appropriate in place of convert().

But the real question of course is what is "appropriate" for the OP's
situation --- he didn't specify exactly what he's got in his bytea
column or how that should be converted to varchar.

            regards, tom lane

Tom Lane wrote:
>> You cannot specify a conversion function while altering
>> a column's type, you'd have to use a new column like this:
>
> Sure you can; that's the whole point of the USING option.
> It'd look something like
>
> ALTER TABLE test ALTER COLUMN val TYPE varchar(255) USING convert(val);

I didn't know that such an option exists.
Thanks for the correction!

Yours,
Laurenz Albe