Re: [HACKERS] Problems on NUMERIC - Mailing list pgsql-hackers

>
> >     First I wonder why the can_coerce... stuff is  #if'd  out  of
> >     parse_relation.c?     For     the     NUMERIC     type    the
> >     numeric(num,typmod) must be called if someone does an
> >
> >         INSERT INTO ... SELECT * FROM ...
> >
> >     But it isn't. It is only called when there  are  calculations
> >     done  on the columns. I also checked that for BPCHAR type and
> >     it simply throws an ERROR  if  the  target's  length  doesn't
> >     match.
>
> Sorry, I'm having trouble thinking of a case which does not behave
> properly with the existing types. I've tried inserting varchar(10)
> columns into a varchar(1) column, I've tried inserting int columns into
> float columns, etc etc. How are you getting handleTargetColname() /
> checkTargetTypes() called where it is rejecting things?

    pgsql=> create table t1 (a char(10));
    CREATE
    pgsql=> create table t2 (a char(4));
    CREATE
    pgsql=> insert into t2 select * from t1;
    ERROR:  Length of a is not equal to the length of target column a
    pgsql=>

>
> It may be that splitting that attribute field into two pieces for
> NUMERIC is opening a can of worms, since there are specific assumptions
> about what that field means throughout the code :(

    It  doesn't  produce  any  problems  so  far,  only  that the
    function numeric(num,typmod) isn't called when doing a  plain
    INSERT ... SELECT.  It is only called when comparisions where
    performed in the SELECT clause of the INSERT on  the  numeric
    attributes.  But  I  need that call to force the rounding and
    range check at INSERT time.  Otherwise,  the  values  in  the
    target  table  will  output  later  with  the  scale of their
    original source table, and that's wrong.  Also  it  would  be
    possible  to insert 1000.0 into a numeric(5,2) attribute, and
    that shouldn't be.

    Maybe I have to hook for NUMERIC there in parse_relation too.
    Up  to  now I'm compiling the whole thing as loadable module.
    I'll check it that's possible when moving it to the builtins.

    But  in  general I think if there is a function with the same
    name as a  type,  that  take  this  type  plus  another  int4
    argument,  this  must  be a range checker/padder/truncator or
    the like and it should be called before values  are  assigned
    to attributes.

>
> Maybe we should think about how to isolate the type-specific
> interpretation of that attribute field into a type-specific handler
> routine? Ooh, that sounds like a pain...

    Noooooooooo


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #