Postgres Pro
Downloads
Home   >   mailing lists

Thread: Feature request: Improve allowed values for generate series

Feature request: Improve allowed values for generate series

From
Eugen Konkov
Date:
Hello Pgsql-hackers,

Seems I fall into corner case:

test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
ERROR:  step size cannot equal zero

But:
test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' );
    generate_series     
------------------------
 2020-11-09 00:00:00+02
 2020-11-10 00:00:00+02
(2 rows)

Here  we  start  at  2020-11-09, add interval of one day and finish at 2020-11-10
Done! series is generated.

In  first  case  I  expect that I start at 2020-11-09, add interval of zero and finish at 2020-11-09
Everything is consistent.

test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
    generate_series
------------------------
 2020-11-09 00:00:00+02
(1 row)


So   I   feature  request  to  allow  zero size step for cases when  start point is equest to finish

What do you think?


-- 
Best regards,
Eugen Konkov

Re: Feature request: Improve allowed values for generate series

From
Eugen Konkov
Date:
Hello Eugen,

Wednesday, November 11, 2020, 8:50:59 PM, you wrote:

> Hello Pgsql-hackers,

> Seems I fall into corner case:

test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
> ERROR:  step size cannot equal zero

> But:
test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' );
>     generate_series     
> ------------------------
>  2020-11-09 00:00:00+02
>  2020-11-10 00:00:00+02
> (2 rows)

> Here  we  start  at  2020-11-09, add interval of one day and finish at 2020-11-10
> Done! series is generated.

> In  first  case  I  expect that I start at 2020-11-09, add interval of zero and finish at 2020-11-09
> Everything is consistent.

test=>> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
>     generate_series
> ------------------------
>  2020-11-09 00:00:00+02
> (1 row)


> So   I   feature  request  to  allow  zero size step for cases when start point is equest to finish

> What do you think?



hm....  probably  with  step 0 we always should generate series of one
value and exit, despite on finish value.
Because  with  step  0 we always stay at current position, so there is
always should be just one value.

-- 
Best regards,
Eugen Konkov

Re: Feature request: Improve allowed values for generate series

From
Pavel Stehule
Date:


st 11. 11. 2020 v 19:59 odesílatel Eugen Konkov <kes-kes@yandex.ru> napsal:
Hello Pgsql-hackers,

Seems I fall into corner case:

test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
ERROR:  step size cannot equal zero

But:
test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-10', INTERVAL '1 day' );
    generate_series     
------------------------
 2020-11-09 00:00:00+02
 2020-11-10 00:00:00+02
(2 rows)

Here  we  start  at  2020-11-09, add interval of one day and finish at 2020-11-10
Done! series is generated.

In  first  case  I  expect that I start at 2020-11-09, add interval of zero and finish at 2020-11-09
Everything is consistent.

test=> SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '00:00:00' );
    generate_series
------------------------
 2020-11-09 00:00:00+02
(1 row)


So   I   feature  request  to  allow  zero size step for cases when  start point is equest to finish

What do you think?

What is the real use case?  Current implementation is very simple - increment should not be zero, and then we know so there is no infinity cycle.

Regards

Pavel





--
Best regards,
Eugen Konkov



Re: Feature request: Improve allowed values for generate series

From
"David G. Johnston"
Date:
On Wed, Nov 11, 2020 at 11:59 AM Eugen Konkov <kes-kes@yandex.ru> wrote:
So   I   feature  request  to  allow  zero size step for cases when  start point is equest to finish

What do you think?

I don't see how this is useful.  If they are equal and you use a non-zero step you get back the one record you are looking for anyway, plus the non-zero step allows them to be unequal.  If zero step is allowed it is only useful for when they are equal, being undefined when they are unequal.

David J.


Re: Feature request: Improve allowed values for generate series

From
"David G. Johnston"
Date:
On Wed, Nov 11, 2020 at 12:12 PM Eugen Konkov <kes-kes@yandex.ru> wrote:

> So   I   feature  request  to  allow  zero size step for cases when start point is equest to finish

> What do you think?



hm....  probably  with  step 0 we always should generate series of one
value and exit, despite on finish value.
Because  with  step  0 we always stay at current position, so there is
always should be just one value.


How is this better than writing "VALUES (start date)"?

David J.

Re: Feature request: Improve allowed values for generate series

From
"David G. Johnston"
Date:
On Wed, Nov 11, 2020 at 7:54 PM Eugen Konkov <kes-kes@yandex.ru> wrote:
Hello David,

I have a table with services, each service have a period. After which service is auto renewal

Services also could be one-time. At this case its interval is '00:00:00'

In which case the concept of interval is undefined - there is no meaningful "second date" here, just the one expiration date - yet you are choosing to keep it in order to introduce an artificial similarity between one-time service and auto-renewal service.  This use case isn't convincing for me.  Writing the one-time service query without generate_series leaves out extraneous stuff that isn't important, which I would recommend even if generate_series were to work as described.

If you are going to introduce code-specific stuff to make this work just write: SELECT * FROM generate_series( '2020-11-09', '2020-11-09', INTERVAL '1000 years' );  It is just as much a construction of code as the other.

David J.