Re: numeric cast oddity

Re: numeric cast oddity

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

Re: numeric cast oddity

Sim Zacks  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

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

Re: numeric cast oddity

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:


  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

Re: numeric cast oddity

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
>   

Re: numeric cast oddity

Sim Zacks  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

Re: numeric cast oddity

Scott Marlowe  writes:
> On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane  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

Re: numeric cast oddity

Re: numeric cast oddity

Re: numeric cast oddity

On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane  wrote:
> Sim Zacks  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