SEO

#1 (**permalink**) 04-10-2008, 11:00 AM

The following bug has been logged online:

Bug reference: 3506
Logged by: Laurent Martelli
Email address: lmartelli@seditel.fr
PostgreSQL version: 7.4,8.1
Operating system: 7.4->Linux, 8.1->Windows
Description: to_number silently ignore characters
Details:

to_number('123.0','999999') returns 1230, at least on version 7.4 and 8.1. I
think it should return 123 or raise an error.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#2 (**permalink**) 04-10-2008, 11:00 AM

Laurent Martelli wrote:
> to_number('123.0','999999') returns 1230, at least on version 7.4 and 8.1. I
> think it should return 123 or raise an error.

to_number will silently ignore any character that doesn't match the
pattern. That can be confusing, and not generally a very bright idea in
applications, but we can't really change the behavior because that could
break existing applications.

You can use the pattern '999999.' to get the behavior you want (returns
123).

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#3 (**permalink**) 04-10-2008, 11:00 AM

Heikki Linnakangas a écrit :
> Laurent Martelli wrote:
>> to_number('123.0','999999') returns 1230, at least on version 7.4 and 8.1. I
>> think it should return 123 or raise an error.
>
> to_number will silently ignore any character that doesn't match the
> pattern. That can be confusing, and not generally a very bright idea in
> applications, but we can't really change the behavior because that could
> break existing applications.

OK. I'm tempted to say that since this is not specified in the
documentation, applications should not rely on this behaviour. But I
understand you will not to break existing applications, so perhaps you
could add a function that would behave in a more expected manner, or at
least document precisely the current behaviour in the documentation.

Best regards,
Laurent

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#4 (**permalink**) 04-10-2008, 11:00 AM

2007/8/3, William Leite Araújo <william.bh@gmail.com>:
>
> CREATE OR REPLACE my_to_number(text,text) RETURNS number AS

OOOps... CREATE OR REPLACE FUNCTION ...

$$
> BEGIN
> IF NOT textregexeq($1,'[0-9]{'||length($2)||'}') THEN
> RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
> END IF;
> RETURNS to_number($1,$2);
> END;
> $$ LANGUAGE PLPGSQL IMMUTABLE;
>
> OR something like ...
>
> 2007/8/3, Laurent Martelli < laurent.martelli@seditel.fr>:
> >
> > Heikki Linnakangas a écrit :
> > > Laurent Martelli wrote:
> > >> to_number('123.0','999999') returns 1230, at least on version 7.4 and
> > 8.1. I
> > >> think it should return 123 or raise an error.
> > >
> > > to_number will silently ignore any character that doesn't match the
> > > pattern. That can be confusing, and not generally a very bright idea
> > in
> > > applications, but we can't really change the behavior because that
> > could
> > > break existing applications.
> >
> > OK. I'm tempted to say that since this is not specified in the
> > documentation, applications should not rely on this behaviour. But I
> > understand you will not to break existing applications, so perhaps you
> > could add a function that would behave in a more expected manner, or at
> > least document precisely the current behaviour in the documentation.
> >
> > Best regards,
> > Laurent
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
>
> --
> William Leite Araújo
> DBA - QUALICONSULT
>

--
William Leite Araújo
Estudante de paternidade - 17a semana

#5 (**permalink**) 04-10-2008, 11:00 AM

Ok. Now the function is OK....

CREATE OR REPLACE FUNCTION my_to_number(text,text) RETURNS numeric AS
$$
BEGIN
IF NOT textregexeq($1,'^[0-9]+$') THEN
RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
END IF;
RETURN to_number($1,$2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

Tests...

=# select my_to_number('123.0','999999');
ERRO: TEXT 123.0 is not a valid NUMBER
=# select my_to_number('1230','999999');
my_to_number
--------------
1230
(1 registro)

Tempo: 0,734 ms

--
William Leite Araújo
DBA - QualiConsult

#6 (**permalink**) 04-10-2008, 11:00 AM

CREATE OR REPLACE my_to_number(text,text) RETURNS number AS
$$
BEGIN
IF NOT textregexeq($1,'[0-9]{'||length($2)||'}') THEN
RAISE EXCEPTION 'TEXT % is not a valid NUMBER',$1;
END IF;
RETURNS to_number($1,$2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

OR something like ...

2007/8/3, Laurent Martelli <laurent.martelli@seditel.fr>:
>
> Heikki Linnakangas a écrit :
> > Laurent Martelli wrote:
> >> to_number('123.0','999999') returns 1230, at least on version 7.4 and
> 8.1. I
> >> think it should return 123 or raise an error.
> >
> > to_number will silently ignore any character that doesn't match the
> > pattern. That can be confusing, and not generally a very bright idea in
> > applications, but we can't really change the behavior because that could
> > break existing applications.
>
> OK. I'm tempted to say that since this is not specified in the
> documentation, applications should not rely on this behaviour. But I
> understand you will not to break existing applications, so perhaps you
> could add a function that would behave in a more expected manner, or at
> least document precisely the current behaviour in the documentation.
>
> Best regards,
> Laurent
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
William Leite Araújo
DBA - QUALICONSULT

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

BUG #3506: to_number silently ignore characters