Thread: BUG #3506: to_number silently ignore characters
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.
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
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
2007/8/3, William Leite Araújo <william.bh@gmail.com>:
OOOps... CREATE OR REPLACE FUNCTION ...
--
William Leite Araújo
Estudante de paternidade - 17a semana
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
Ok. Now the function is OK....<br /><br /><span style="font-weight: bold;">CREATE OR REPLACE FUNCTION</span>
my_to_number(text,text)<span style="font-weight: bold;">RETURNS</span> numeric <span style="font-weight: bold;">AS
</span><br/>$$ <br /> <span style="font-weight: bold;">BEGIN</span> <br /> <span style="font-weight:
bold;">IFNOT </span>textregexeq($1,'^[0-9]+$') <span style="font-weight: bold;">THEN</span> <br />
<spanstyle="font-weight: bold;">RAISE EXCEPTION</span> 'TEXT % is not a valid NUMBER',$1; <br /> <span
style="font-weight:bold;">END IF;</span><br /> <span style="font-weight: bold;">RETURN</span> to_number($1,$2);
<br/><span style="font-weight: bold;"> END;</span><br />$$ <span style="font-weight: bold;">LANGUAGE PLPGSQL
IMMUTABLE;<br/><br /></span>Tests...<br /><br />=# select my_to_number('123.0','999999');<br />ERRO: TEXT 123.0 is not
avalid NUMBER<br />=# select my_to_number('1230','999999');<br /> my_to_number<br />--------------<br />
1230<br/>(1 registro)<br /><br />Tempo: 0,734 ms<br /><br />-- <br />William Leite Araújo<br />DBA - QualiConsult <br
/>
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 ...
--
William Leite Araújo
DBA - QUALICONSULT
$$
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