Thread: change data type from text to numeric

Daniel Torres wrote
> Hi everyone,
>
> sorry to bother you with a simple question, (I'm a new user of
> postgresql),
> how do I change the data type of a column of text, to numeric or integer?
>
> I tried with:
>
> ALTER TABLE table_name
>  ALTER COLUMN col_name TYPE integer
> ;
>
> It gives me a mistake
> ERROR:  la columna  no puede convertirse automáticamente al tipo integer
> SUGERENCIA:  Especifique una expresión USING para llevar a cabo la
> conversión.
>
> translation: the column can't be converted automatically to type integer
> .... specify an expression using USING to make the conversion.
>
> I also tried on pgadmin3, but couldn't found how to do it...
>
> thanks,
> Daniel

There is a serviceable example of the USING variation of ALTER TABLE ..
ALTER COLUMN in the documentation.

http://www.postgresql.org/docs/9.3/interactive/sql-altertable.html

The simplest conversion is simply "col_name::numeric"...

David J.




--
View this message in context: http://postgresql.nabble.com/change-data-type-from-text-to-numeric-tp5828434p5828438.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Wed, 26 Nov 2014 15:40:53 -0600
Daniel Torres <nobeeakon@gmail.com> wrote:

> Hi everyone,
>
> sorry to bother you with a simple question, (I'm a new user of postgresql),
> how do I change the data type of a column of text, to numeric or integer?
>
> I tried with:
>
> ALTER TABLE table_name
>  ALTER COLUMN col_name TYPE integer
> ;
>
> It gives me a mistake
> ERROR:  la columna  no puede convertirse automáticamente al tipo integer
> SUGERENCIA:  Especifique una expresión USING para llevar a cabo la
> conversión.
>
> translation: the column can't be converted automatically to type integer
> .... specify an expression using USING to make the conversion.

See the docs for ALTER TABLE, the section on USING:
http://www.postgresql.org/docs/9.3/static/sql-altertable.html
Error messages are your friend, read them.

But short answer:

ALTER TABLE table_name
  ALTER COLUMN col_name TYPE integer
  USING CAST(col_name AS INT);

Which will work as long as all the values can be cast to an INT without
error. If you have values that can't be cast without error, you'll have
to fix them before you can do the ALTER.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com