Change type of varchar field to integer: "cannot be cast automatically to type integer"

Question

I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible.

Is there a way around this or should I just create another table and bring the records into it using a query.

The field contains only integer values.

Answer 1

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.

Answer 2

this worked for me.

change varchar column to int

change_column :table_name, :column_name, :integer

got:

PG::DatatypeMismatch: ERROR:  column "column_name" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

chnged to

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

Answer 3

You can do it like:

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

or try this:

change_column :table_name, :column_name, :integer, using: 'column_name::integer'

If you are interested to find more about this topic read this article: https://kolosek.com/rails-change-database-column

Answer 4

Try this, it will work for sure.

When writing Rails migrations to convert a string column to an integer you'd usually say:

change_column :table_name, :column_name, :integer

However, PostgreSQL will complain:

PG::DatatypeMismatch: ERROR:  column "column_name" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted. Just say this in your migration:

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

The above will mimic what you know from other database adapters. If you have non-numeric data, results may be unexpected (but you're converting to an integer, after all).

Answer 5

I got the same problem. Than I realized I had a default string value for the column I was trying to alter. Removing the default value made the error go away :)

Answer 6

I had the same issue. I started to reset the default of the column.

change_column :users, :column_name, :boolean, default: nil
change_column :users, :column_name, :integer, using: 'column_name::integer', default: 0, null: false

Answer 7

If you are working on development environment(or on for production env. it may be backup your data) then first to clear the data from the DB field or set the value as 0.

UPDATE table_mame SET field_name= 0;

After that to run the below query and after successfully run the query, to the schemamigration and after that run the migrate script.

ALTER TABLE table_mame ALTER COLUMN field_name TYPE numeric(10,0) USING field_name::numeric;

I think it will help you.

Answer 8

If you've accidentally or not mixed integers with text data you should at first execute below update command (if not above alter table will fail):

UPDATE the_table SET col_name = replace(col_name, 'some_string', '');

Answer 9

Empty Strings or Nulls in character varying

If you have a column with empty strings or nulls, you may run into the error message:

invalid input syntax for type integer: ""

Solution

use coalesce and a nullif to get empty strings and nulls to become a zero value.

ALTER TABLE peopleGroup ALTER numberPeople TYPE INT USING (cast ( coalesce( nullif( trim(numberPeople), '' ), '0' ) as integer ))