Конвертация данных при изменении типа столбца таблицы PostgreSQL

    Изменение типа данных столбца таблицы в PostgreSQL делается с использованием команды ALTER TABLE в комбинации с ALTER COLUMN. Согласно документации эта операция "будет успешна, только если все существующие значения в столбце могут быть неявно приведены к новому типу". Но это не совсем верно. Например, мешают еще связанные с этим столбцом ограничения DEFAULT и CHECK, или несовместимость типов данных. Тип VARCHAR(4) можно легко сменить на CHAR(4) или наоборот, а попытка сменить на INTEGER приведет к ошибке. И эта ошибка будет даже для пустой таблицы.

    Если PostgreSQL не справляется с приведением типов, то нужно ему подсказать, как это сделать. У ALTER COLUMN для этого есть дополнительный параметр USING. Рассмотрим решение "трудностей" изменение типа данных столбца таблицы с CHAR\VARCHAR на INTEGER на примере. Создадим таблицу "test"

CREATE TABLE test
( 
  id     SERIAL PRIMARY KEY, 
  name   TEXT, 
  status CHAR(1) NOT NULL DEFAULT '0'
);

и изменим тип столбца status с CHAR(1) на INTEGER.

Если команда

ALTER TABLE test ALTER COLUMN status TYPE CHAR(10);

будет успешной, то команда

ALTER TABLE test ALTER COLUMN status TYPE INTEGER;

приведет к ошибке "столбец status нельзя автоматически привести к типу integer". Нам надо: удалить ограничение DEFAULT, поменять тип с явным приведением к INTEGER и создать новое ограничение DEFAULT:

ALTER TABLE test ALTER COLUMN status DROP DEFAULT;
ALTER TABLE test ALTER COLUMN status TYPE INTEGER USING (status::INTEGER);
ALTER TABLE test ALTER COLUMN status SET DEFAULT 0;

Посмотрим, что у нас получилось:

Тип поля status сменился на INTEGER.

    Параметр USING позволяет использовать более сложные конструкции, чем простое преобразование типов. Например, в него можно вставить CASE или вызов функции. Создадим таблицу и вставим в нее несколько строк

CREATE TABLE test
(
  id SERIAL PRIMARY KEY, 
  name TEXT, 
  status CHAR(1)
);

INSERT INTO test(name, status) 
  VALUES ('цифра 0', '0'),
         ('цифра 3', '3'),
         ('цифра 9', '9'),
         ('буква X', 'X');

Наличие строки со status равным "X" при изменении типа столбца status на INTEGER приведет "USING (status::INTEGER)" к ошибке "неверный синтаксис для типа integer: X". Итак задача: значения столбца равные "0" преобразовать к -1, цифры от "1" до "9" в число, а все остальные символы в их ASCII-код:

ALTER TABLE test 
  ALTER COLUMN status TYPE INTEGER USING (CASE 
                                            WHEN status = '0'  THEN -1
                                            WHEN status ~ '\d' THEN status::INTEGER 
                                            ELSE ASCII(status) 
                                          END);

Мы видим снова успешный результат: