Casting Varchar to Numeric

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/

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, 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/

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.

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

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.

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

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

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 ;)

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