Re: Casting bytea to varchar

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: Casting bytea to varchar
Дата
Msg-id A737B7A37273E048B164557ADEF4A58B0579988C@ntex2010a.host.magwien.gv.at
обсуждение исходный текст
Ответ на Casting bytea to varchar  ("Rajagopalan, Jayashree" <Jayashree.Rajagopalan@emc.com>)
Ответы Re: Casting bytea to varchar  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Albe Laurenz
Дата:
Сообщение: Re: Postgres WAL Recovery Fails... And Then Works...
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: corrupted indexes when using base backups generated from hot standby