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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
