Обсуждение: Casting bytea to varchar
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
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