Thread: Casting Varchar to Numeric
Just undertaking a port of a small Data Warehouse-type batch load application from Oracle to PostgreSQL. It's common practice in such a system to load data into a staging table whose fields are typically all varying text. Then the conversion and mapping of the data is done as a second stage into the 'proper' tables. In Oracle, you can put a text field into a numeric field as long as it contains a number and teh conversion will be implicit (or you can simply specify to_number(value)) to make the conversion explicitly. In PostgreSQL, it seems that an error is thrown up if an implicit conversion is attempted, and the only explicit conversion seems to be to_number(value, format) which doesn't do what's needed generically enough. I can't believe that this isn't possible - can anyone point me in the right direction? Cheers Andy Marden
On Tue, Nov 27, 2001 at 09:55:41AM -0000, Andy Marden wrote:
> Just undertaking a port of a small Data Warehouse-type batch load
> application from Oracle to PostgreSQL. It's common practice in such a system
> to load data into a staging table whose fields are typically all varying
> text. Then the conversion and mapping of the data is done as a second stage
> into the 'proper' tables.
>
> In Oracle, you can put a text field into a numeric field as long as it
> contains a number and teh conversion will be implicit (or you can simply
> specify to_number(value)) to make the conversion explicitly.
>
> In PostgreSQL, it seems that an error is thrown up if an implicit conversion
> is attempted, and the only explicit conversion seems to be to_number(value,
> format) which doesn't do what's needed generically enough.
If you can't cast it by to_number() in you query (why, are you use COPY?)
you can try define trigger that cast it internaly by to_number() or
other way.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Tue, Nov 27, 2001 at 09:55:41AM -0000, Andy Marden wrote: > Just undertaking a port of a small Data Warehouse-type batch load > application from Oracle to PostgreSQL. It's common practice in such a system > to load data into a staging table whose fields are typically all varying > text. Then the conversion and mapping of the data is done as a second stage > into the 'proper' tables. > > In Oracle, you can put a text field into a numeric field as long as it > contains a number and teh conversion will be implicit (or you can simply > specify to_number(value)) to make the conversion explicitly. > > In PostgreSQL, it seems that an error is thrown up if an implicit conversion > is attempted, and the only explicit conversion seems to be to_number(value, > format) which doesn't do what's needed generically enough. > > I can't believe that this isn't possible - can anyone point me in the right > direction? Well, it's a bit more complicated than that. For example, text -> int4 is done. You do realise you can cast like value::type. For example: select field::numeric(10,2); Now, I'm not sure if you can do varchar -> numeric directly. I don't use varchar anymore since there is no advantage over text but text is much better supported. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
On Tue, 27 Nov 2001, Andy Marden wrote: > In PostgreSQL, it seems that an error is thrown up if an implicit conversion > is attempted, and the only explicit conversion seems to be to_number(value, > format) which doesn't do what's needed generically enough. > > I can't believe that this isn't possible - can anyone point me in the right > direction? I don't think anyone's written numeric(text) yet, so there is currently no conversion function for the two types. If you write one and name it as above, you should be able to get conversions between the two types.
Well, I've finally sorted it out and can now convert text/varchar fields into numeric I cannot BELIEVE I've had to resort to such things. I've changed all my varchar fields to text and then applied this to them (column name is 'litre_amount'): translate (litre_amount, '.', '')::integer / case strpos(litre_amount, '.') when 0 then 1 else (10^(char_length (litre_amount) - strpos(litre_amount, '.'))) end works for positive/negative and with/without decinal point. You could equally do this straight from varchar I would imagine with: translate (litre_amount, '.', '')::text::integer / case strpos(litre_amount, '.') when 0 then 1 else (10^(char_length (litre_amount) - strpos(litre_amount, '.'))) end Would be pretty could is some could implement this in PostgreSQL natively (and more quickly!). Why not let to_number and to_char work as in the Oracle way and generically cast numerical fields back and forth into strings. This kind of thing makes people give up at the first hurdle when they start looking at products. Cheers Andy Marden
On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote: > Well, I've finally sorted it out and can now convert text/varchar fields > into numeric I cannot BELIEVE I've had to resort to such things. > > I've changed all my varchar fields to text and then applied this to them > (column name is 'litre_amount'): <snip ugly conversion method> Does this work? select litre_amount::float::numeric; With the column still as varchar, this worked for me: select litre_amount::text::float::numeric from temp2; Long-winded definitly. You can create your own conversion function to automaticaly convert text -> numeric. <untested!> create function numeric(text) returns numeric as 'select $1::float8::numeric' language 'sql'; The problem is that postgres has an extrememly generic type system and it has no idea about promoting types. For example, you get a problem when comparing an int4 to an int8. Should you convert both arguments to int4's or int8's? *We* know the answer but postgres doesn't. This problem extends to anywhere where multiple types do similar things: int2, int4, int8 float4, float8, numeric text, varchar, char HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
Martijn van Oosterhout wrote:
> On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote:
> > Well, I've finally sorted it out and can now convert text/varchar fields
> > into numeric I cannot BELIEVE I've had to resort to such things.
> >
> > I've changed all my varchar fields to text and then applied this to them
> > (column name is 'litre_amount'):
>
> <snip ugly conversion method>
>
> Does this work?
>
> select litre_amount::float::numeric;
Maybe it works, but with the step through float you loose
precision. In the old days where the type input/output
functions wheren't protected, one was able to use
select numeric_in(textout(litre_amount)) from ...
Well, some thought it'd not be such a good idea to let end
users muck around with C string pointers, and IIRC I was one
of them.
But there are still the internal casting capabilities of
PL/pgSQL. What about
CREATE FUNCTION to_numeric ( text ) RETURNS numeric AS '
BEGIN
RETURN $1;
END;' LANGUAGE 'plpgsql';
Maybe this function is far too overcomplicated and someone
might enhance the algorithm :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Wed, Dec 05, 2001 at 05:09:48PM -0500, Jan Wieck wrote:
> Martijn van Oosterhout wrote:
> > On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote:
> > > Well, I've finally sorted it out and can now convert text/varchar fields
> > > into numeric I cannot BELIEVE I've had to resort to such things.
> > >
> > > I've changed all my varchar fields to text and then applied this to them
> > > (column name is 'litre_amount'):
> >
> > <snip ugly conversion method>
> >
> > Does this work?
> >
> > select litre_amount::float::numeric;
>
> Maybe it works, but with the step through float you loose
> precision. In the old days where the type input/output
> functions wheren't protected, one was able to use
>
> select numeric_in(textout(litre_amount)) from ...
>
> Well, some thought it'd not be such a good idea to let end
> users muck around with C string pointers, and IIRC I was one
> of them.
>
> But there are still the internal casting capabilities of
> PL/pgSQL. What about
>
> CREATE FUNCTION to_numeric ( text ) RETURNS numeric AS '
> BEGIN
> RETURN $1;
> END;' LANGUAGE 'plpgsql';
>
> Maybe this function is far too overcomplicated and someone
> might enhance the algorithm :-)
We already have to_number() that cast from string to numeric...
test=# SELECT to_number('1234.5678', '9999999999999999.999999999999999999');
to_number
-----------
1234.5678
(1 row)
... small problem is that you must set expectant format of string.
http://www.postgresql.org/idocs/index.php?functions-formatting.html
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Martijn, It does work (believe it or not). I've now tried the method you mention below - that also works and is much nicer. I can't believe that PostgreSQL can't work this out. Surely implementing an algorithm that understands that if you can go from a ->b and b->c then you can certainly go from a->c. If this is viewed as too complex a task for the internals - at least a diagram or some way of understanding how you should go from a->c would be immensely helpful wouldn't it! Daunting for anyone picking up the database and trying to do something simple(!) Thanks for your help. Andy "Martijn van Oosterhout" <kleptog@svana.org> wrote in message news:20011129105642.A31599@svana.org... > On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote: > > Well, I've finally sorted it out and can now convert text/varchar fields > > into numeric I cannot BELIEVE I've had to resort to such things. > > > > I've changed all my varchar fields to text and then applied this to them > > (column name is 'litre_amount'): > > <snip ugly conversion method> > > Does this work? > > select litre_amount::float::numeric; > > With the column still as varchar, this worked for me: > > select litre_amount::text::float::numeric from temp2; > > Long-winded definitly. You can create your own conversion function to > automaticaly convert text -> numeric. <untested!> > > create function numeric(text) returns numeric > as 'select $1::float8::numeric' language 'sql'; > > The problem is that postgres has an extrememly generic type system and it > has no idea about promoting types. For example, you get a problem when > comparing an int4 to an int8. Should you convert both arguments to int4's or > int8's? *We* know the answer but postgres doesn't. > > This problem extends to anywhere where multiple types do similar things: > > int2, int4, int8 > float4, float8, numeric > text, varchar, char > > HTH, > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Terrorists can only take my life. Only my government can take my freedom. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
On Mon, 3 Dec 2001, Andy Marden wrote: > Martijn, > > It does work (believe it or not). I've now tried the method you mention > below - that also works and is much nicer. I can't believe that PostgreSQL > can't work this out. Surely implementing an algorithm that understands that > if you can go from a ->b and b->c then you can certainly go from a->c. If It's more complicated than that (and postgres does some of this but not all), for example the cast text->float8->numeric potentially loses precision and should probably not be an automatic cast for that reason. > this is viewed as too complex a task for the internals - at least a diagram > or some way of understanding how you should go from a->c would be immensely > helpful wouldn't it! Daunting for anyone picking up the database and trying > to do something simple(!) There may be a need for documentation on this. Would you like to write some ;)
The whole point is that someone should be able to pick up PostgreSQL and use it. All this rigmorole is not well documented.What is wrong with the equivalent of Oracle's to_char and to_number? It works and hasn't caused anyone, to my knowledge, sleeplessnights. Sure it's great re the extra types and ability to cast via different routes, but don't scare people off at the first hurdle!This flexibility should be a bonus not a requirement. Andy ----- Original Message ----- From: "Jan Wieck" <janwieck@yahoo.com> To: "Martijn van Oosterhout" <kleptog@svana.org> Cc: "Andy Marden" <amarden@usa.net>; <pgsql-general@postgresql.org> Sent: Wednesday, December 05, 2001 10:09 PM Subject: Re: [GENERAL] Casting Varchar to Numeric > Martijn van Oosterhout wrote: > > On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote: > > > Well, I've finally sorted it out and can now convert text/varchar fields > > > into numeric I cannot BELIEVE I've had to resort to such things. > > > > > > I've changed all my varchar fields to text and then applied this to them > > > (column name is 'litre_amount'): > > > > <snip ugly conversion method> > > > > Does this work? > > > > select litre_amount::float::numeric; > > Maybe it works, but with the step through float you loose > precision. In the old days where the type input/output > functions wheren't protected, one was able to use > > select numeric_in(textout(litre_amount)) from ... > > Well, some thought it'd not be such a good idea to let end > users muck around with C string pointers, and IIRC I was one > of them. > > But there are still the internal casting capabilities of > PL/pgSQL. What about > > CREATE FUNCTION to_numeric ( text ) RETURNS numeric AS ' > BEGIN > RETURN $1; > END;' LANGUAGE 'plpgsql'; > > Maybe this function is far too overcomplicated and someone > might enhance the algorithm :-) > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > >
On Wed, 5 Dec 2001, Andy Marden wrote: > The whole point is that someone should be able to pick up PostgreSQL > and use it. All this rigmorole is not well documented. What is wrong > with the equivalent of Oracle's to_char and to_number? It works and > hasn't caused anyone, to my knowledge, sleepless nights. Sure it's > great re the extra types and ability to cast via different routes, but > don't scare people off at the first hurdle! This flexibility should be > a bonus not a requirement. AFAICS 7.1.3 and 7.2 both describe a to_number that converts text to numeric although I haven't used them. But that's still different from casting.
> On Mon, 3 Dec 2001, Andy Marden wrote:
>
> > Martijn,
> >
> > It does work (believe it or not). I've now tried the method you mention
> > below - that also works and is much nicer. I can't believe that PostgreSQL
> > can't work this out. Surely implementing an algorithm that understands that
> > if you can go from a ->b and b->c then you can certainly go from a->c. If
>
> It's more complicated than that (and postgres does some of this but not
> all), for example the cast text->float8->numeric potentially loses
> precision and should probably not be an automatic cast for that reason.
>
> > this is viewed as too complex a task for the internals - at least a diagram
> > or some way of understanding how you should go from a->c would be immensely
> > helpful wouldn't it! Daunting for anyone picking up the database and trying
> > to do something simple(!)
>
> There may be a need for documentation on this. Would you like to write
> some ;)
OK, I ran some tests:
test=> create table test (x text);
CREATE
test=> insert into test values ('323');
INSERT 5122745 1
test=> select cast (x as numeric) from test;
ERROR: Cannot cast type 'text' to 'numeric'
I can see problems with automatically casting numeric to text because
you have to guess the desired format, but going from text to numeric
seems quite easy to do. Is there a reason we don't do it?
I can cast to integer and float8 fine:
test=> select cast ( x as integer) from test;
?column?
----------
323
(1 row)
test=> select cast ( x as float8) from test;
?column?
----------
323
(1 row)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can see problems with automatically casting numeric to text because
> you have to guess the desired format, but going from text to numeric
> seems quite easy to do. Is there a reason we don't do it?
I do not think it's a good idea to have implicit casts between text and
everything under the sun, because that essentially destroys the type
checking system. What we need (see previous discussion) is a flag in
pg_proc that says whether a type conversion function may be invoked
implicitly or not. I've got no problem with offering text(numeric) and
numeric(text) functions that are invoked by explicit function calls or
casts --- I just don't want the system trying to use them to make
sense of a bogus query.
> I can cast to integer and float8 fine:
I don't believe that those should be available as implicit casts either.
They are, at the moment:
regression=# select 33 || 44.0;
?column?
----------
3344
(1 row)
Ugh.
regards, tom lane
Added to TODO.detail/typeconv. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I can see problems with automatically casting numeric to text because > > you have to guess the desired format, but going from text to numeric > > seems quite easy to do. Is there a reason we don't do it? > > I do not think it's a good idea to have implicit casts between text and > everything under the sun, because that essentially destroys the type > checking system. What we need (see previous discussion) is a flag in > pg_proc that says whether a type conversion function may be invoked > implicitly or not. I've got no problem with offering text(numeric) and > numeric(text) functions that are invoked by explicit function calls or > casts --- I just don't want the system trying to use them to make > sense of a bogus query. > > > I can cast to integer and float8 fine: > > I don't believe that those should be available as implicit casts either. > They are, at the moment: > > regression=# select 33 || 44.0; > ?column? > ---------- > 3344 > (1 row) > > Ugh. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026