Thread: numeric cast oddity

When I cast an integer to numeric using :: notation it ignores the scale
and precision that I specify, but when I use the cast function it uses
the scale and precision that I specify.

Sim

select version();
"PostgreSQL 8.3.5 on i586-pc-linux-gnu, compiled by GCC
i586-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)"

select -1::numeric(20,4)
?column?
numeric
------------
-1.0000

select cast(-1 as numeric(20,4))
numeric
numeric(20,4)
-------------------
-1.0000

Sim Zacks <sim@compulab.co.il> writes:
> When I cast an integer to numeric using :: notation it ignores the scale
> and precision that I specify, but when I use the cast function it uses
> the scale and precision that I specify.

Really?  Your example doesn't seem to show that.

            regards, tom lane

On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sim Zacks <sim@compulab.co.il> writes:
>> When I cast an integer to numeric using :: notation it ignores the scale
>> and precision that I specify, but when I use the cast function it uses
>> the scale and precision that I specify.
>
> Really?  Your example doesn't seem to show that.

I think he's talking about the headers

It is more then just a
headers issue. I have a view that has a column of type numeric(20,4).
I modified the view and
added a union which cast an integer as a numeric(20,4) using the ::
notation.
I received an error
stating that I could not change the column type.
When I used the cast
function notation it allowed it through.


The fact that it
actually converts it to numeric(20,4) doesn't help me if the view
thinks that it is a regular numeric.


Sim





Scott Marlowe wrote:

<blockquote
 cite="mid:dcc563d10912030749r2bc35040qd42da5b29c37193f@mail.gmail.com"
 type="cite">
  On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:


    Sim Zacks <sim@compulab.co.il> writes:


      When I cast an integer to numeric using :: notation it ignores the scale
and precision that I specify, but when I use the cast function it uses
the scale and precision that I specify.


    Really?  Your example doesn't seem to show that.



I think he's talking about the headers

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Really? �Your example doesn't seem to show that.

> I think he's talking about the headers

The headers I get are

regression=# select -1::numeric(20,4);
 ?column?
----------
  -1.0000
(1 row)

regression=# select cast(-1 as numeric(20,4));
 numeric
---------
 -1.0000
(1 row)

which are indeed different (might be worth looking into why) but
don't seem to have anything to do with scale/precision.

            regards, tom lane

I wrote:
> which are indeed different (might be worth looking into why)

Oh: the reason they're different is that these expressions are not
actually the same thing.  Minus binds less tightly than typecast.
You get consistent results if you input equivalent expressions:

regression=# select cast(-1 as numeric(20,4));
 numeric
---------
 -1.0000
(1 row)

regression=# select (-1)::numeric(20,4);
 numeric
---------
 -1.0000
(1 row)

regression=# select - cast(1 as numeric(20,4));
 ?column?
----------
  -1.0000
(1 row)

regression=# select - 1::numeric(20,4);
 ?column?
----------
  -1.0000
(1 row)

What we're actually seeing here is that the code to guess a default
column name doesn't descend through a unary minus operator, it just
punts upon finding an Op node.

            regards, tom lane

As I mentioned, it is more then just a headers issue it is a type issue.

I have a view that has a column of type numeric(20,4)

If I replace that column with -1::numeric(20,4) or - (1::numeric(20,4))
the type that goes to the view is numeric without any scale or precision
and then I get an error that I cannot change the column type.

It seems to be a negative number issue because if I use a positive
number or null it gives me the numeric(20,4) it is only for negative
that it gives me the numeric without scale or precision.


Sim



Tom Lane wrote:

> I wrote:
>
>> which are indeed different (might be worth looking into why)
>>
>
> Oh: the reason they're different is that these expressions are not
> actually the same thing.  Minus binds less tightly than typecast.
> You get consistent results if you input equivalent expressions:
>
> regression=# select cast(-1 as numeric(20,4));
>  numeric
> ---------
>  -1.0000
> (1 row)
>
> regression=# select (-1)::numeric(20,4);
>  numeric
> ---------
>  -1.0000
> (1 row)
>
> regression=# select - cast(1 as numeric(20,4));
>  ?column?
> ----------
>   -1.0000
> (1 row)
>
> regression=# select - 1::numeric(20,4);
>  ?column?
> ----------
>   -1.0000
> (1 row)
>
> What we're actually seeing here is that the code to guess a default
> column name doesn't descend through a unary minus operator, it just
> punts upon finding an Op node.
>
>             regards, tom lane
>

Sim Zacks <sim@compulab.co.il> writes:
> If I replace that column with -1::numeric(20,4) or - (1::numeric(20,4))
> the type that goes to the view is numeric without any scale or precision
> and then I get an error that I cannot change the column type.

You've still got the order of operations wrong.
    (-1)::numeric(20,4)
is known to have typmod (20,4), because the cast operation enforces it.
    - (1::numeric(20,4))
is not known to have any particular typmod --- it actually does fit in
(20,4), of course, but that fact depends on the detailed behavior of the
minus operator, which is not known to the type machinery.

            regards, tom lane