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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     gt  CREATE TABLE test  x varchar    CREATE TABLE   gt  insert into test x  values   14       42      INSERT 0 2   gt  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     gt  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

User · Answer

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

User · Answer

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

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

Examples related to postgresql

Examples related to casting

Examples related to postgresql-9.1

Examples related to fieldtype