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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general