CODEWITHSUNDEEP
sqlpostgresql
ddd
ddd

Reputation: 5029

Why can't I modify column type in Postgres

I am trying to change type of columns from varchar to integer. When I run the following command

alter table audio_session
alter column module_type_cd type INT USING module_type_cd::integer,
alter column sound_type_cd type INT USING sound_type_cd::integer

I got the error:

ERROR:  invalid input syntax for integer: "string"
SQL state: 22P02

The error does not make any sense. There is no "string" in the command at all. What did I do wrong?

Upvotes: 0

Views: 71

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

The columns audio_session.module_type_cd or audio_session.sound_type_cd has at least one non-numeric value among all values of each row. So, it's impossible to convert to a completely numeric data type.

As an example :

create table Table1( col1 varchar(10), col2 varchar(10) );
insert into Table1 values('1','1');
insert into Table1 values('2','2');
insert into Table1 values('3','C3');

select CAST(coalesce(col1, '0') AS integer) as converted from Table1; -- this works
select CAST(coalesce(col2, '0') AS integer) as converted from Table1; -- but, this doesn't

SQL Fiddle Demo

Upvotes: 2

klin
klin

Reputation: 121644

There is no "string" in the command at all.

But must be in the data. The command you're going to use tries to cast all the columns values to integer. Check this:

select *
from audio_session
where module_type_cd = 'string' or sound_type_cd = 'string';

Upvotes: 2

Related Questions