How to create generate_series function in postgreSQL?

Question

I saw at many places on the internet that you could use generate_series function (in postgreSQL) like shown below:

select k, percentile_disc(k) within group (order by things.value)
from things, generate_series(0.01, 1, 0.01) as k
group by k

And it works like :-

generate_series generates temporary table with values between a starting and ending value, with an optional step. In this example, generate_series(0.01, 1, 0.01) returns 0.01, 0.02, etc..

But in my case it shows error saying it does not exist.

QL Error [42883]: ERROR: function generate_series(numeric, integer, numeric) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

Could anyone please help me with the code on how to create this function explicitly.

It will be of great help!

What version version of Postgres are you using? This code should work (I mean, it works when I try it). — Gordon Linoff
– Gordon Linoff, Commented Jul 1, 2020 at 11:59

Gordon Linoff · Accepted Answer · 2020-07-01 12:01:04Z

1

Your code should work. But you can just use integers and divide:

select k / 100.0, percentile_disc(k / 100.0) within group (order by things.value)
from things cross join
     generate_series(1, 100, 1) as k
group by k

I want to point out that even this code works:

select generate_series(0.01::numeric, 1::int, 0.01::numeric)

And tested going back to Postgres 9.5 on db<>fiddle.

answered Jul 1, 2020 at 12:01

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@Nick . . . Probably. Flavors should do us the favor by identifying themselves in error messages.

Laurenz Albe Over a year ago

No, the correct thing would be to add a tag for the fork and the version.

Collectives™ on Stack Overflow

How to create generate_series function in postgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related