Thread: Casting Varchar to Numeric

Casting Varchar to Numeric

From
"Andy Marden"
Date:
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



Re: Casting Varchar to Numeric

From
Karel Zak
Date:
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

Re: Casting Varchar to Numeric

From
Martijn van Oosterhout
Date:
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.

Re: Casting Varchar to Numeric

From
Stephan Szabo
Date:
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.


Re: Casting Varchar to Numeric

From
"Andy Marden"
Date:
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




Re: Casting Varchar to Numeric

From
Martijn van Oosterhout
Date:
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.

Re: Casting Varchar to Numeric

From
Jan Wieck
Date:
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


Re: Casting Varchar to Numeric

From
Karel Zak
Date:
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

Re: Casting Varchar to Numeric

From
"Andy Marden"
Date:
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



Re: Casting Varchar to Numeric

From
Stephan Szabo
Date:
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 ;)


Re: Casting Varchar to Numeric

From
"Andy Marden"
Date:
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
>
>


Re: Casting Varchar to Numeric

From
Stephan Szabo
Date:
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.



Re: Casting Varchar to Numeric

From
Bruce Momjian
Date:
> 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

Re: Casting Varchar to Numeric

From
Tom Lane
Date:
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

Re: Casting Varchar to Numeric

From
Bruce Momjian
Date:
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