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