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
2009/12/3 Sim Zacks <sim@compulab.co.il>
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.0000select cast(-1 as numeric(20,4))
numeric
numeric(20,4)
-------------------
-1.0000
That looks right to me. What you've effectively asked for is
-0000000000000001. 0000, which resolves to -1.000.
Regards
Thom
2009/12/3 Sim Zacks <sim@compulab.co.il>
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.0000select cast(-1 as numeric(20,4))
numeric
numeric(20,4)
-------------------
-1.0000
I've just spotted what you mean. Ignore my previous response.
Thom
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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body style="direction: ltr;" bgcolor="#ffffff" text="#000000">
<p style="margin-bottom: 0cm; margin-top: 0pt;">It is more then just a
headers issue. I have a view that has a column of type numeric(20,4).</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">I modified the view and
added a union which cast an integer as a numeric(20,4) using the ::
notation.</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">I received an error
stating that I could not change the column type.</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">When I used the cast
function notation it allowed it through.</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;"><br>
</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">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.</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;"><br>
</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">Sim<br>
</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;"><br>
</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;"><br>
</p>
<p style="margin-bottom: 0cm; margin-top: 0pt;">Scott Marlowe wrote:<br>
</p>
<blockquote
cite="mid:dcc563d10912030749r2bc35040qd42da5b29c37193f@mail.gmail.com"
type="cite">
<pre wrap="">On Thu, Dec 3, 2009 at 8:03 AM, Tom Lane <a class="moz-txt-link-rfc2396E" href="mailto:tgl@sss.pgh.pa.us"><tgl@sss.pgh.pa.us></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Sim Zacks <a class="moz-txt-link-rfc2396E" href="mailto:sim@compulab.co.il"><sim@compulab.co.il></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap="">Really? Your example doesn't seem to show that.
</pre>
</blockquote>
<pre wrap=""><!---->
I think he's talking about the headers
</pre>
</blockquote>
</body>
</html>
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:
Show quoted text
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