Rectangle 27 0

PG::DatatypeMismatch: ERROR:  column "column_name" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

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

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

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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

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.

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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:

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

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
USING

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

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"

@itsols 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.

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

@itsols 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/

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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

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

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

PG::DatatypeMismatch: ERROR:  column "column_name" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

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

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

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

Be careful with defaults as well

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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.

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

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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:

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

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
USING

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

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"

@itsols 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.

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

@itsols 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/

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype
Rectangle 27 0

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

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.

postgresql - Change type of varchar field to integer: "cannot be cast ...

postgresql casting postgresql-9.1 fieldtype