Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    28

    Unanswered: cannot add a fractional number to numeric field

    hi all,

    i am working with Visual Basic & postgres. using ADO and ODBC to connect to postgres. when i run the following query to update a
    numeric(19,4) field an error occurs.

    Update Accounts Set ClosingBal = ClosingBal + 900.50 Where AcCode = 105;
    the error message is

    "ERROR: Unable to identify an operator '+' for types 'numeric' and 'double precision'
    You will have to retype this query using an explicit cast" .

    if the value is a whole number like 900, the same query works fine.
    and also if i use an explicit cast it works correctly. but i cannot use casts always because this numeric field is used for many calculations and queries.

    can we create a userdefined + operator to avoid explicit casting.
    or can we set any option to use automatic casting in postgres. there will not be any problem in automatic casting.

    if i change the data type to float insted of numeric it will work corectly.
    but i need a monitory precision of 4 digits.
    is there any data type that can be used to store money.


    pls help...
    thanks...

  2. #2
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up u can

    You can simply prepare your vb varaible directly than update with output
    X=Y+500

    [Update Mytable set myrow=X where ... ]
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  3. #3
    Join Date
    Jan 2002
    Posts
    19

    Re: u can

    Originally posted by karim2k
    You can simply prepare your vb varaible directly than update with output
    X=Y+500

    [Update Mytable set myrow=X where ... ]
    I not agree with your solution, the user is trying to do: x=x+500
    this mean that is more easier to do it in one shot.

    Try to do a cast before.


    Gaetano

  4. #4
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Talking AnyWay

    May be ...
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  5. #5
    Join Date
    Jan 2002
    Posts
    19

    Re: AnyWay

    Originally posted by karim2k
    May be ...
    With a temp variable the user should do a SELECT .... FOR UPDATE
    and after the UPDATE.

  6. #6
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Yes

    Yes , I admit
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  7. #7
    Join Date
    May 2003
    Posts
    28
    thanks all,
    finally i find many solutions to the problem myself. First solution is as Gaetano suggest cast the value before. Like,
    set x=x+ cast(10.55 as numeric) where ...

    but i have to cast in all queries.

    yet another solution is to find the value first and update in another query as karim2k suggest.

    The better solution is to add a + operator that handles this situation (numeric + float8). i created two operators + & - to solve the problem.
    Last edited by Jinujose; 05-27-03 at 00:27.

  8. #8
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    cool

    Yeah , please tell with more details , I myself developp with vb too.
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  9. #9
    Join Date
    May 2003
    Posts
    28

    Cool

    use the following queries to add two fuctions(to handle operation) and the two operators + & - . i think that u understand what the
    queries for...

    is this a better solution than so many casts. u have to run the following queries in all the databses.

    CREATE FUNCTION "numeric_add" (numeric, double precision) RETURNS numeric AS'
    select numeric_add($1,cast($2 as numeric));
    'LANGUAGE 'sql' WITH (isstrict);

    CREATE OPERATOR + ( PROCEDURE = "numeric_add",
    LEFTARG = numeric, RIGHTARG = double precision,
    COMMUTATOR = +);

    CREATE FUNCTION "numeric_sub" (numeric, double precision) RETURNS numeric AS'
    select numeric_sub($1,cast($2 as numeric));
    'LANGUAGE 'sql' WITH (isstrict);

    CREATE OPERATOR - ( PROCEDURE = "numeric_sub",
    LEFTARG = numeric, RIGHTARG = double precision,
    COMMUTATOR = -);


    need more details....?

  10. #10
    Join Date
    Jan 2002
    Posts
    19
    In order to optimize the code you can also declare both functions:
    WITH ( isStrict, isCachable )

    anyway is better not use anymore WITH but the new way to specify
    these "modifiers":

    CREATE FUNCTION "numeric_add" (numeric, double precision) RETURNS numeric AS'
    select numeric_add($1,cast($2 as numeric));
    'LANGUAGE 'sql' STRICT, IMMUTABLE ;

    and the same of numeric_sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •