Casting bytea to varchar

[Rajagopalan, Jayashree]

Hi:

I'm relatively new to postgres:

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.

Thanks
Jayashree

[Albe Laurenz]

Post by Rajagopalan, Jayashree
I've to alter a column which is of datatype bytea to varchar(255).
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

[Tom Lane]

Post by Albe Laurenz
You cannot specify a conversion function while altering

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

[Albe Laurenz]

Post by Tom Lane

Post by Albe Laurenz
You cannot specify a conversion function while altering

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