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 Management System 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

Tue, 1 Oct 2002 16:17:01 +0200
"Mario Weilguni" <mario.weilguni@icomedias.com>
floor function in 7.3b2
"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

Tue, 01 Oct 2002 10:31:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: floor function in 7.3b2 
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 section in the documentation that might help users to handle this?
Maybe we can collect such reports and prepare a upgrade tutorial?

Regards,
	Mario


Wed, 2 Oct 2002 08:02:46 +0200
Mario Weilguni <mweilguni@sime.com>
some more minor incompatibilties 7.2 <-> 7.3
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

Wed, 02 Oct 2002 09:07:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: some more minor incompatibilties 7.2 <-> 7.3 

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, Pennsylvania 19073

Thu, 3 Oct 2002 22:26:30 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: floor function in 7.3b2
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


04 Oct 2002 02:31:22 -0400
Neil Conway <neilc@samurai.com>
Re: floor function in 7.3b2
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

Fri, 04 Oct 2002 09:37:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
numeric hierarchy again (was Re: 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
  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, Pennsylvania 19073

Fri, 4 Oct 2002 12:01:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: numeric hierarchy again (was Re: floor function in 7.3b2)
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

Fri, 04 Oct 2002 12:10:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: numeric hierarchy again (was Re: floor function in 7.3b2) 
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, Pennsylvania 19073

Fri, 4 Oct 2002 13:45:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: numeric hierarchy again (was Re: floor function in 7.3b2)
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

Fri, 04 Oct 2002 15:07:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: numeric hierarchy again (was Re: floor function in 7.3b2) 