Пишу, по большей части, про историю, свою жизнь и немного про программирование.

Особенности смены типа в PostgreSQL: text → bytea

При обыденной смене типа колонки в «Постгресе» с текстового типа на бинарный получил ошибку «ERROR: invalid input syntax for type bytea». Запрос довольно рядовой и сюрпризов я не ожидал:

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING colmn::bytea;

Решения нагуглить не удалось, поэтому начал исследовать проблему сам. Бинарным поиском нашёл строку на которой запрос запнулся, потом так же вычислил символ. Им оказался обратный слэш. Заэкранировал его и ошибка пропала:

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING REPLACE(colmn, '\', '\\')::bytea;

Всё отработало нормально, сконвертировалось, слеши получаются из базы как и ожидается — одинарными.

3 комментария
Алекс 2017

Дурацкий способ конвертации, конечно, гораздо правильнее использовать функцию convert_to. И костылей не понадобится, и отработает, подозреваю, быстрее.

Евгений Степанищев (bolknote.ru) 2017

Комментарий для Алекс:

Это указание типа-то (CAST) — это дурацкий способ конвертации? Тем более в базе, которую свою кодировку знает?

Евгений Степанищев (bolknote.ru) 2017

Комментарий для Алекс:

Вот, кстати, разница:

doc=# select count(convert_to(extra, ’utf8’)) from medo_sync;
ERROR: character with byte sequence 0x98 in encoding «WIN1251» has no equivalent in encoding «UTF8»

И

doc=# select count(replace(extra, ’\’, ’\\’)::BYTEA) from medo_sync;

 count

-​-​-​-​-​-​-​

 47148

(1 row)