CODEWITHSUNDEEP
sqlpostgresqlfunctional-programming
varun
varun

Reputation: 1

How to create generate_series function in postgreSQL?

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!

Upvotes: 0

Views: 1793

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271221

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.

Upvotes: 1

Related Questions