Re: change data type from text to numeric

On Wed, 26 Nov 2014 15:40:53 -0600
Daniel Torres <nobeeakon(at)gmail(dot)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