Thread: BUG #13587: Lag Default option does not work with floats and reals

The following bug has been logged on the website:

Bug reference:      13587
Logged by:          Robert McGehee
Email address:      rmcgehee@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Linux / Redhat 7
Description:

The lag() window function is documented to accept a default argument.
However, if lag is applied to either a float or a real (and possibly other
types as well), adding a default gives an error. As this behavior for
reals/floats does not match the documentation, I believe it to be a bug.
Below is an example showing that lag() works as document for integers, but
not on a real column.


CREATE TABLE _test1 (a INTEGER);
CREATE TABLE _test2 (a REAL);
INSERT INTO _test1 VALUES (1);
INSERT INTO _test1 VALUES (2);
INSERT INTO _test2 VALUES (1);
INSERT INTO _test2 VALUES (2);

SELECT a, lag(a, 1)    OVER (ORDER BY a) FROM _test1;
SELECT a, lag(a, 1, 0) OVER (ORDER BY a) FROM _test1;

SELECT a, lag(a, 1)    OVER (ORDER BY a) FROM _test2;
SELECT a, lag(a, 1, 0) OVER (ORDER BY a) FROM _test2;

rmcgehee@gmail.com writes:
> The lag() window function is documented to accept a default argument.
> However, if lag is applied to either a float or a real (and possibly other
> types as well), adding a default gives an error.

The default argument has to be the same type as the value argument;
in your failing example, it is not.  Perhaps the documentation could be
more explicit about that.

            regards, tom lane

Ahh, thanks for this!
On Tue, Aug 25, 2015 at 5:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> rmcgehee@gmail.com writes:
> > The lag() window function is documented to accept a default argument.
> > However, if lag is applied to either a float or a real (and possibly
> other
> > types as well), adding a default gives an error.
>
> The default argument has to be the same type as the value argument;
> in your failing example, it is not.  Perhaps the documentation could be
> more explicit about that.
>
>                         regards, tom lane
>