Pg datatypemismatch ошибка столбец нельзя автоматически привести к типу integer

There is no implicit (automatic) cast from text or varchar to integer (i.e. you cannot pass a varchar to a function expecting integer or assign a varchar field to an integer one), so you must specify an explicit cast using ALTER TABLE … ALTER COLUMN … TYPE … USING:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);

Note that you may have whitespace in your text fields; in that case, use:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);

to strip white space before converting.

This shoud’ve been obvious from an error message if the command was run in psql, but it’s possible PgAdmin-III isn’t showing you the full error. Here’s what happens if I test it in psql on PostgreSQL 9.2:

=> CREATE TABLE test( x varchar );
CREATE TABLE
=> insert into test(x) values ('14'), (' 42  ');
INSERT 0 2
=> ALTER TABLE test ALTER COLUMN x TYPE integer;
ERROR:  column "x" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion. 
=> ALTER TABLE test ALTER COLUMN x TYPE integer USING (trim(x)::integer);
ALTER TABLE        

Thanks @muistooshort for adding the USING link.

See also this related question; it’s about Rails migrations, but the underlying cause is the same and the answer applies.

If the error still occurs, then it may be related not to column values, but indexes over this column or column default values might fail typecast. Indexes need to be dropped before ALTER COLUMN and recreated after. Default values should be changed appropriately.

I experienced this error when I tried to run rake db:migrate.
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::DatatypeMismatch: ERROR: column «developer_id» cannot be cast automatically to type integer
HINT: You might need to specify «USING developer_id::integer».

I am changing my sqlite3 database to PostgreSQL for deployment in Heroku.
I am assuming the error is coming from the db/migrate file.rb that has the developer_id column specified.

  def change
    change_column :entries, :developer_id, :integer
  end
end

I will add any other document that may help locate the problem.

Нет неявного (автоматического) перевода из text или varchar в integer (т.е. вы не можете передать varchar функции, ожидающей integer, или назначьте поле varchar для integer one), поэтому вы должны указать явное преобразование, используя ALTER TABLE… ALTER COLUMN… TYPE… ИСПОЛЬЗОВАНИЕ:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);

Обратите внимание, что у вас могут быть пробелы в текстовых полях; в этом случае используйте:

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);

чтобы удалить пробел перед преобразованием.

Это сообщение было очевидно из сообщения об ошибке, если команда была запущена в psql, но возможно, что PgAdmin-III не показывает вам полную ошибку. Вот что произойдет, если я тестирую его в psql на PostgreSQL 9.2:

=> CREATE TABLE test( x varchar );
CREATE TABLE
=> insert into test(x) values ('14'), (' 42  ');
INSERT 0 2
=> ALTER TABLE test ALTER COLUMN x TYPE integer;
ERROR:  column "x" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion. 
=> ALTER TABLE test ALTER COLUMN x TYPE integer USING (trim(x)::integer);
ALTER TABLE        

Спасибо @muistooshort за добавление ссылки USING.

См. также этот связанный вопрос; это о миграции Rails, но основная причина — то же самое и ответ применяется.

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

    Если PostgreSQL не справляется с приведением типов, то нужно ему подсказать, как это сделать. У ALTER COLUMN для этого есть дополнительный параметр USING. Рассмотрим решение «трудностей» изменение типа данных столбца таблицы с CHARVARCHAR на 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);

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