Обсуждение: floor function in 7.3b2
I noticed that some of my queries don't work anymore because they're using
the floor function:
e.g.: select type, floor(date_part('epoch', dataend)) as ts from
last_modified
Why is floor not working anymore? I guess I can use round too, but I don't
want to modify semantics.
Regards,Mario Weilguni
icomedias <communication solutions/> graz . berlin
-------------------------------------------------------icomedias ist Hersteller von ico>>cms: Information-und Content
ManagementSystem für Inter- UND INTRAnet
-------------------------------------------------------
Mario Weilguni icomedias gmbh
software engineering entenplatz 1
tel: +43-316-721.671-17 8020 graz, austria
fax: +43-316-721.671-26 http://www.icomedias.com
e-mail: mario.weilguni@icomedias.com
"Mario Weilguni" <mario.weilguni@icomedias.com> writes:
> I noticed that some of my queries don't work anymore because they're using
> the floor function:
> e.g.: select type, floor(date_part('epoch', dataend)) as ts from
> last_modified
> Why is floor not working anymore?
Mph. Seems we have floor(numeric) but not floor(float8), and the latter
is what you need here.
You could cast date_part's result to numeric; or perhaps you could use
trunc() which exists in both numeric and float8 flavors. It's got
different semantics for negative inputs though.
For 7.4 we should take another look at the operator/function set and
fill in this hole and any others like it.
regards, tom lane
I noticed some other minor differences between 7.2 and 7.3: * 7.2: select now() + '1 minute'::timespan => works * 7.2: select now() + '1 minute'::reltime => works * 7.3: select now() + '1 minute'::timespan => does not work (Type "timespan" does not exist) * 7.3 select now() + '1 minute'::reltime => works So timespan is no longer supported I guess, but reltime will work as well. Is there a compatibility or migration sectionin the documentation that might help users to handle this? Maybe we can collect such reports and prepare a upgrade tutorial? Regards,Mario
Mario Weilguni <mweilguni@sime.com> writes: > So timespan is no longer supported I guess, but reltime will work as > well. Is there a compatibility or migration section in the > documentation that might help users to handle this? The release notes are still in a pretty crude state, but they do mention this issue: : The last vestiges of support for type names datetime and timespan are : gone; use timestamp and interval instead See http://developer.postgresql.org/docs/postgres/release.html regards, tom lane
Added to TODO:
* Add floor(float8) and other missing functions
---------------------------------------------------------------------------
Tom Lane wrote:
> "Mario Weilguni" <mario.weilguni@icomedias.com> writes:
> > I noticed that some of my queries don't work anymore because they're using
> > the floor function:
> > e.g.: select type, floor(date_part('epoch', dataend)) as ts from
> > last_modified
> > Why is floor not working anymore?
>
> Mph. Seems we have floor(numeric) but not floor(float8), and the latter
> is what you need here.
>
> You could cast date_part's result to numeric; or perhaps you could use
> trunc() which exists in both numeric and float8 flavors. It's got
> different semantics for negative inputs though.
>
> For 7.4 we should take another look at the operator/function set and
> fill in this hole and any others like it.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Tom Lane <tgl@sss.pgh.pa.us> writes: > "Mario Weilguni" <mario.weilguni@icomedias.com> writes: > > Why is floor not working anymore? > > Mph. Seems we have floor(numeric) but not floor(float8), and the latter > is what you need here. Sorry, I missed much of the casting discussion -- but is there a reason why we can't cast from float8 -> numeric implicitely? IIRC the idea was to allow implicit casts from lower precision types to higher precision ones. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes:
> Sorry, I missed much of the casting discussion -- but is there a
> reason why we can't cast from float8 -> numeric implicitely? IIRC the
> idea was to allow implicit casts from lower precision types to higher
> precision ones.
The implicit casting hierarchy is now
int2 -> int4 -> int8 -> numeric -> float4 -> float8
Moving to the left requires an explicit cast (or at least an assignment
to a column). I know this looks strange to someone who knows that our
numeric type beats float4/float8 on both range and precision, but it's
effectively mandated by the SQL spec. Any combination of "exact" and
"inexact" numeric types is supposed to yield an "inexact" result per
spec, thus numeric + float8 yields float8 not numeric. Another reason
for doing it this way is that a numeric literal like "123.456" can be
initially typed as numeric, and later implicitly promoted to float4 or
float8 if context demands it. Doing that the other way 'round would
introduce problems with precision loss. We had speculated about
introducing an "unknown_numeric" pseudo-type to avoid that problem, but
the above hierarchy eliminates the need for "unknown_numeric". We can
initially type a literal as the smallest thing it will fit in, and then
do implicit promotion as needed. (7.3 is not all the way there on that
plan, but 7.4 will be.)
regards, tom lane
Tom Lane wrote: > Moving to the left requires an explicit cast (or at least an assignment > to a column). I know this looks strange to someone who knows that our > numeric type beats float4/float8 on both range and precision, but it's > effectively mandated by the SQL spec. Any combination of "exact" and > "inexact" numeric types is supposed to yield an "inexact" result per > spec, thus numeric + float8 yields float8 not numeric. Another reason > for doing it this way is that a numeric literal like "123.456" can be > initially typed as numeric, and later implicitly promoted to float4 or > float8 if context demands it. Doing that the other way 'round would > introduce problems with precision loss. We had speculated about > introducing an "unknown_numeric" pseudo-type to avoid that problem, but > the above hierarchy eliminates the need for "unknown_numeric". We can > initially type a literal as the smallest thing it will fit in, and then > do implicit promotion as needed. (7.3 is not all the way there on that > plan, but 7.4 will be.) Do we know that defaulting floating constants will not be a performance hit? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Do we know that defaulting floating constants will not be a performance
> hit?
Uh ... what's your concern exactly? The datatype coercion (if any) will
happen once at parse time, not at runtime.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Do we know that defaulting floating constants will not be a performance > > hit? > > Uh ... what's your concern exactly? The datatype coercion (if any) will > happen once at parse time, not at runtime. Yes, I realize it is during parsing. I was just wondering if making constants coming in from the parser NUMERIC is a performance hit? I see in gram.y that FCONST comes in as a Float so I don't even see were we make it NUMERIC. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I realize it is during parsing. I was just wondering if making
> constants coming in from the parser NUMERIC is a performance hit?
Offhand I don't see a reason to think that coercing to NUMERIC (and then
something else) is slower than coercing to FLOAT (and then something else).
Yeah, you would come out a little behind when the final destination type
is FLOAT, but on the other hand you win a little when it's NUMERIC.
I see no reason to think this isn't a wash overall.
> I see
> in gram.y that FCONST comes in as a Float so I don't even see were we
> make it NUMERIC.
It's make_const in parse_node.c that has the first contact with the
grammar's output. Up to that point the value's just a string, really.
The grammar does *not* coerce it to float8.
regards, tom lane