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?
Christine Penner
Ingenious Software
250-352-9495
christ...@ingenioussoftware.com
--
Sent via pgsql-general
In response to Christine Penner christ...@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
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
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?
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
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
On Wed, 2010-02-24 at 20:22 +, 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
In response to Raymond O'Donnell r...@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 christ...@ingenioussoftware.com:
I have a character field I want to change to a number. The values in
that field are all
In response to Joshua D. Drake j...@commandprompt.com:
On Wed, 2010-02-24 at 20:22 +, 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
On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake j...@commandprompt.com wrote:
On Wed, 2010-02-24 at 20:22 +, 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
On 24/02/10 20:27, Joshua D. Drake wrote:
On Wed, 2010-02-24 at 20:22 +, 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.
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.
Joshua D. Drake j...@commandprompt.com writes:
On Wed, 2010-02-24 at 20:22 +, 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
On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Joshua D. Drake j...@commandprompt.com writes:
On Wed, 2010-02-24 at 20:22 +, 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
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 Wed, Feb 24, 2010 at 1:43 PM, Tom Lane
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
In response to Raymond O'Donnell r...@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.
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
18 matches
Mail list logo
