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.

Dionisio

What specific ALTER TABLE did you try and what was the specific error message? – Reflate

@muistooshort I tried using alter from phppgadmin. Selected the column and tried to input the new field type. The error is: SQL error: ERROR: column "MID" cannot be cast to type integer – Dionisio

First is to backup table. Then you may create an another column (say field2) of integer type in the same table. Select the cast to integer value of the field1 into field2 . Then rename the column. – Rosaniline

@Rosaniline but the new column falls at the end of the table right? Can't I have it in the same position? – Dionisio

yes you'll have it last. – Rosaniline

hmm, try this one postgresonline.com/journal/archives/… – Rosaniline

@Dionisio Caring about column positions is usually a sign of iffy application design. You almost always want to be using explicitly named columns and SELECT lists, not relying on column ordinal positions. That said, the approach given in the answers will preserve column position. – Isopropanol

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.

Isopropanol

Thank you for taking the time. But I cannot seem to get this working. I tried your ALTER line and it gives me an error "Syntax error near Using" – Dionisio

My statement: ALTER TABLE "tblMenus" ALTER COLUMN "MID" USING (trim("MID")::integer); – Dionisio

@Dionisio Entirely my mistake; I corrected it just as I saw your comment. See revised. It was right in the demo code, just not the generic example at the start. – Isopropanol

Thanks a million! This answer saved me a lot of trouble and time. I wonder why niether phppgadmin nor pgadmin have this as a feature... – Dionisio

@Dionisio Most of the core team isn't that interested in PgAdmin, and few of them use it. It has some annoying usability warts and functionality limitations. This is only one of many of them. Because few experts use PgAdmin they aren't as motivated to fix the things that would annoy them about it. I don't use it myself, because I find psql much quicker and easier. I wrote a bit of a rant about PgAdmin usability with regards to backup and restore a while ago: blog.ringerc.id.au/2012/05/… – Isopropanol

Thanks for that update on your blog. Even I don't like wx and that's why I got out of CodeBlocks and switched to Lazarus. I just couldn't get wx properly working on either Ubuntu or Windows. But then again, I think Lazarus also uses it but it just works out of the box. Furthermore, I didn't like QT because of licensing and availability. Oops, this is going in a different direction ;) Thanks again! – Dionisio

@CraigRinger I was reading some other answer of yours, and asked you here by mistake.. Damnn!! Sorry :p – Looselimbed

I tried this and it didn't work when the table didn't even have any rows at all. – We

Be careful with executing this in PostgresSQL as i had ran that code and it stated it was successful, but when i looked at the table structure.... nothing had changed. In my case I'm in a position to drop and re-create the table. – Budwig

What happens if column contains non-numeric values, like digits with letters. What happens to letters after cast? – Satisfactory

@Satisfactory Try it and see. 'foo'::integer . You get an error raised. I don't recall off the top of my head if postgres has non-fatal casts, but you can pattern match the string against a regexp or like expression and skip the cast if it doesn't look sensible. – Isopropanol

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)'

Handclasp

did you try this exercise with data and was your data intact? – Dionisio

as long as what is in the column is an integer, yes – Handclasp

It doesn't work with me. I'm using ruby 2.2.3 with rails 4.2.3 – Cranny

@ThinhD.Bui - Works for me, 2.3.0, rails 4.2.6 – Juggle

Be careful with defaults as well – Pubilis

Did this still works in MySQL? 'integer USING CAST(column_name AS integer)'. oh, the answer is: NO. syntax error in MySQL. No way to fit both. – Boatswain

doesn't work, PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "" error raises – Lasky

Excellent answer. This worked for me. change_column :rating_factors, :deductible_factor, 'decimal USING CAST(deductible_factor AS decimal)' – Afro

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

Meakem

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).

Isidor

I just wanted to add one more point that, be careful with change_column. it is irreversible. I suggest using up and down in migration to make this reversible. – Utica

PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "" error happens – Lasky

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 :)

Unsettled

Also existing indexes on this column might be a problem. They need to be dropped before ALTER and recreated after. – Cantonment

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

Brought

Thank you. In my case casting to boolean wasnt necessary – Galloromance

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.

Gleanings

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', '');

Warnke

You'd be better off with something like regexp_replace(col_name, '[^0-9.]','','g') if you're trying to strip unwanted characters and white-space. You'd need something a bit more sophisticated if you want to retain NaN and Inf and 10E42 scientific notation, though. – Isopropanol

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 ))

Tagalog