Cast char to number

In response to Christine Penner <christine@ingenioussoftware.com>:

I have a character field I want to change to a number. The values in
that field are all numbers that may or may not be padded with spaces
or 0's. What is the best way to do that?

Put the values in numeric fields to begin with and cast to chars as
needed. Basically reverse what you're doing.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

I don't understand what you mean. This is a column in a table that is
already a char and has numbers in it. I want it to be a number field
not character. How can I change the data type of that column without
loosing the data I have in it?

Christine

At 11:38 AM 24/02/2010, you wrote:

On 24/02/2010 19:53, Christine Penner wrote:

I don't understand what you mean. This is a column in a table that is
already a char and has numbers in it. I want it to be a number field not
character. How can I change the data type of that column without loosing
the data I have in it?

Christine

At 11:38 AM 24/02/2010, you wrote:

In response to Christine Penner <christine@ingenioussoftware.com>:

I have a character field I want to change to a number. The values in
that field are all numbers that may or may not be padded with spaces
or 0's. What is the best way to do that?

Put the values in numeric fields to begin with and cast to chars as
needed. Basically reverse what you're doing.

I think what he means is that you should have been doing the reverse to
begin with - storing numbers in the database as numeric columns, and
then casting them to a character format as needed for display.

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
(iv) Rename the new column to the same name as the old column
(v) Recreate any of the constraints dropped in step (iii).

I think the cast in step (ii) might not be necessary - not sure about this.

HTH.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 24/02/10 20:06, Raymond O'Donnell wrote:

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

--
Richard Huxton
Archonet Ltd

Raymond O'Donnell wrote:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
(iv) Rename the new column to the same name as the old column
(v) Recreate any of the constraints dropped in step (iii).

Or try in a single step:
ALTER TABLE tablename ALTER column column_name
TYPE numeric USING column_name::numeric;
(replace numeric by the desired type if it's not numeric).

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

On 24/02/10 20:06, Raymond O'Donnell wrote:

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR: column "id" cannot be cast to type "pg_catalog.numeric"
postgres=#

Joshua D. Drake

--
Richard Huxton
Archonet Ltd

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

In response to Raymond O'Donnell <rod@iol.ie>:

On 24/02/2010 19:53, Christine Penner wrote:

At 11:38 AM 24/02/2010, you wrote:

In response to Christine Penner <christine@ingenioussoftware.com>:

I have a character field I want to change to a number. The values in
that field are all numbers that may or may not be padded with spaces
or 0's. What is the best way to do that?

Put the values in numeric fields to begin with and cast to chars as
needed. Basically reverse what you're doing.

I don't understand what you mean. This is a column in a table that is
already a char and has numbers in it. I want it to be a number field not
character. How can I change the data type of that column without loosing
the data I have in it?

I think what he means is that you should have been doing the reverse to
begin with - storing numbers in the database as numeric columns, and
then casting them to a character format as needed for display.

Actually, I misunderstood the question. I thought you were trying to
figure out how to extract the data for display. But fixing the fields
to be the right type is a noble goal :)

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.
(iv) Rename the new column to the same name as the old column
(v) Recreate any of the constraints dropped in step (iii).

I think the cast in step (ii) might not be necessary - not sure about this.

Agreed. There's a slightly shorter way, you can do:
ALTER TABLE tablename ALTER COLUMN columnname TYPE INT;

If that doesn't work because the cast isn't automatic, you can add a
USING clause:

ALTER TABLE tablename
ALTER COLUMN columnname TYPE INT USING columnname::INT;

(as an example, the using clause may need to be more complicate than that).

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to "Joshua D. Drake" <jd@commandprompt.com>:

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

On 24/02/10 20:06, Raymond O'Donnell wrote:

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR: column "id" cannot be cast to type "pg_catalog.numeric"
postgres=#

Remember that what comes after the using clause can be arbitrarily
complex (I have written ALTER TABLE statements with USING CASE ... that
are pages and pages long to fix data consistency problems in the
same step as correcting a poorly chosen column type ;)

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

On 24/02/10 20:06, Raymond O'Donnell wrote:

However, to address your immediate problem, you could try something like
this:

(i)   Create a new column of type numeric or integer as appropriate.
(ii)  update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
   http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR:  column "id" cannot be cast to type "pg_catalog.numeric"
postgres=#

The example given works fine for me:

smarlowe=# create table abc (c char(10));
CREATE TABLE
smarlowe=# insert into abc values ('0010'),('90'),('66');
INSERT 0 3
smarlowe=# alter table abc alter column c type numeric using c::numeric;
ALTER TABLE

On 24/02/10 20:27, Joshua D. Drake wrote:

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

On 24/02/10 20:06, Raymond O'Donnell wrote:

However, to address your immediate problem, you could try something like
this:

(i) Create a new column of type numeric or integer as appropriate.
(ii) update your_table set new_column = CAST(trim(both ' 0' from
old_column) as numeric)
(iii) Drop the old column, as well as any constraints depending on it.

Or, in any recent version of PG you can do this via ALTER TABLE
http://www.postgresql.org/docs/8.4/static/sql-altertable.html

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

You might want to clean up the values before doing this.

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR: column "id" cannot be cast to type "pg_catalog.numeric"

Well if it's actually "char(10)" or somesuch you need to do a little
more I grant you (though not much). I was assuming varchar myself.

richardh=> CREATE TABLE intastext (i char(10));
CREATE TABLE
richardh=> INSERT INTO intastext (i) VALUES ('1'),
('02'),('3.0'),('3.5'),('X');
INSERT 0 5
richardh=> SELECT * FROM intastext ;
i
------------
1
02
3.0
3.5
X
(5 rows)

richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
i::text::numeric::integer;
ERROR: invalid input syntax for type numeric: "X"
richardh=> DELETE FROM intastext WHERE i = 'X';
DELETE 1
richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
i::text::numeric::integer;
ALTER TABLE
richardh=> SELECT * FROM intastext ;
i
---
1
2
3
4
(4 rows)

Of course "USING" can have any expression to convert the type.

richardh=> CREATE FUNCTION my_map(char(10)) RETURNS integer AS $$ SELECT
CASE WHEN $1>='0' AND $1<='9' THEN $1::numeric::integer ELSE -999 END;
$$ LANGUAGE SQL;
CREATE FUNCTION
richardh=> ALTER TABLE intastext ALTER COLUMN i TYPE integer USING
my_map(i);ALTER TABLE
richardh=> SELECT * FROM intastext ;
i
------
1
2
3
4
-999
(5 rows)

--
Richard Huxton
Archonet Ltd

On Wed, 2010-02-24 at 13:35 -0700, Scott Marlowe wrote:

You might want to clean up the values before doing this.

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR: column "id" cannot be cast to type "pg_catalog.numeric"
postgres=#

The example given works fine for me:

smarlowe=# create table abc (c char(10));
CREATE TABLE
smarlowe=# insert into abc values ('0010'),('90'),('66');
INSERT 0 3
smarlowe=# alter table abc alter column c type numeric using c::numeric;
ALTER TABLE

Well that is interesting. I would have thought it would have failed
because of the padding...

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR: column "id" cannot be cast to type "pg_catalog.numeric"

That just indicates that there isn't an *implicit* coercion from char to
numeric. With a USING clause you can specify an arbitrary conversion.

I agree with the recommendation to test it out before actually doing
the table change though. Maybe look at the results of

select id, id::numeric from your_table

to see if it looks sane for all the different data formats in the
column.

regards, tom lane

On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Wed, 2010-02-24 at 20:22 +0000, Richard Huxton wrote:

ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer;

That won't work in this case. char() can't be cast to int/numeric. Not
only that it isn't possible to clean up the data in table because char
automatically pads.

postgres=# alter table foo alter column id type numeric;
ERROR:  column "id" cannot be cast to type "pg_catalog.numeric"

That just indicates that there isn't an *implicit* coercion from char to
numeric.  With a USING clause you can specify an arbitrary conversion.

I agree with the recommendation to test it out before actually doing
the table change though.  Maybe look at the results of

       select id, id::numeric from your_table

to see if it looks sane for all the different data formats in the
column.

And if the database is mostly sitting idle (i.e. no other users) you
can always just do

begin;
alter table yada;

test how it went and then commit or rollback.

For the OP: It's a bad idea to do that kind of stuff in production
cause you'll put a lock on the table others will have to wait for.

This is what I did.

set all blank columns to '0' because they were causing errors.
alter table T alter column a type integer using a::integer

That worked perfectly.

Thanks everyone for the help.

Christine

At 12:46 PM 24/02/2010, Scott Marlowe wrote:

On 24/02/2010 21:42, Christine Penner wrote:

This is what I did.

set all blank columns to '0' because they were causing errors.
alter table T alter column a type integer using a::integer

That worked perfectly.

Glad you got sorted.

Bill's advice upthread is worth taking on board - if you're in a
position to do so, make sure that numbers are stored as numbers rather
than text, and you'll save yourself all sorts of wacky grief.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

In response to Raymond O'Donnell <rod@iol.ie>:

On 24/02/2010 21:42, Christine Penner wrote:

This is what I did.

set all blank columns to '0' because they were causing errors.
alter table T alter column a type integer using a::integer

That worked perfectly.

Glad you got sorted.

Bill's advice upthread is worth taking on board - if you're in a
position to do so, make sure that numbers are stored as numbers rather
than text, and you'll save yourself all sorts of wacky grief.

As an aside, I feel that this is a natural part of software evolution.
No matter how carefully you try to plan, you end up with someone
saying, "this will be a serial number that will contain both numbers
and letters" ... so you make it a text type field. Then, a year later
you find out that the serial number is really just a number, and you
actually want to be able to do math on it because you can find out
the year the part was designed by dividing by 1000 or something.

You make the best decisions you can based on the available information.
If you get it wrong, there's always ALTER TABLE :)

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On 24/02/10 22:03, Bill Moran wrote:

Then, a year later
you find out that the serial number is really just a number, and you
actually want to be able to do math on it because you can find out
the year the part was designed by dividing by 1000 or something.

You make the best decisions you can based on the available information.
If you get it wrong, there's always ALTER TABLE :)

Coming in 9.1:

ALTER CUSTOMER ... SET REQUIREMENTS ...

--
Richard Huxton
Archonet Ltd