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.

Нет неявного (автоматического) приведения из text or varchar в integer (т.е. вы не можете передать varchar функции, ожидающей integer или назначить varchar поле в integer one), поэтому вы должны указать явное приведение, используя ALTER TABLE… ALTER COLUMN… TYPE… USING:

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, но основная причина та же, и ответ применим.

Если ошибка все еще возникает, то она может быть связана не со значениями столбца, а с индексами по этому столбцу или значениям по умолчанию для столбца может произойти сбой приведения типов. Индексы должны быть удалены до ALTER COLUMN и воссозданы после. Значения по умолчанию должны быть соответствующим образом изменены.

change_column :users, :gender, :integer, using: 'gender::integer'

change_column :users, :gender, 'integer USING CAST(gender AS integer)'

PG::DatatypeMismatch: ERROR:  default for column "gender" cannot be cast automatically to type integer
: ALTER TABLE "users" ALTER COLUMN "gender" TYPE integer USING gender::integer

1 ответ

def up
  execute "ALTER TABLE users ALTER gender DROP DEFAULT;"
  change_column :users, :gender, :integer, using: 'gender::integer', default: 0
end