Re: [HACKERS] floor function in 7.3b2
Tom Lane [EMAIL PROTECTED] writes: Mario Weilguni [EMAIL PROTECTED] 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 [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
numeric hierarchy again (was Re: [HACKERS] floor function in 7.3b2)
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: numeric hierarchy again (was Re: [HACKERS] floor function in 7.3b2)
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: numeric hierarchy again (was Re: [HACKERS] floor function in 7.3b2)
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: numeric hierarchy again (was Re: [HACKERS] floor function in 7.3b2)
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: numeric hierarchy again (was Re: [HACKERS] floor function in 7.3b2)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] floor function in 7.3b2
Mario Weilguni [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
