Change column datatype from Text to Integer in PostgreSQL

Question

I am using the following query to change the data type of a column from text to integer but getting error:

 alter table a.attend alter column terminal TYPE INTEGER ;

ERROR: column "terminal" cannot be cast automatically to type integer

Solution

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test

 --Result--
 id
 character varying
--------------------------
 1
 11
 12

You can see from the above table that I have used the data type – character varying for id column. But it was a mistake because I am always giving integers as id. So using varchar here is a bad practice. So let’s try to change the column type to integer.

ALTER TABLE test ALTER COLUMN id TYPE integer;

But it returns:

ERROR: column “id” cannot be cast automatically to type integer SQL state: 42804 Hint: Specify a USING expression to perform the conversion

That means we can’t simply change the data type because data is already there in the column. Since the data is of type character varying Postgres can't expect it as integer though we entered integers only. So now, as Postgres suggested we can use the USING expression to cast our data into integers.

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id::integer);

It Works.

---

So you should use

alter table a.attend alter column terminal TYPE INTEGER  USING (terminal::integer) ;

Comments

[Erwin Brandstetter]

The USING clause is only required if there is no implicit assignment cast defined between source and target type. Details here and here.

[Spidey]

This is going to sound silly, but don't forget to replace id in USING (id::integer). I accidentally turned one of my columns into a copy of the id column. Lucky for me, I was just experimenting so no harm no foul :D