Connect by level in Postgres on a sequence

Question

I need to convert a query from Oracle to Postgres which uses connect by level on a sequence in Oracle.

I know that connect by level is not available in Postgres. I couldn't find an alternate for this using recursive cte.

In Oracle if I run this query.

 create sequence ID_SEQ;
 select ID_SEQ.nextval from DUAL connect by level <= 3;

I will get the following result

1
2
3

I need the same in Postgres. Please share some solutions if anyone has any idea.

Thanks in advance Gokul.

Possible duplicate of What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH? — Aleksej
– Aleksej, Commented Jan 25, 2018 at 11:49
@Aleksej: no it's not a duplicate. In this query connect by is misused to generate a series of rows (and it's not even officially supported). This is a lot easier in Postgres — user330315
– user330315, Commented Jan 25, 2018 at 11:52

user330315 · Accepted Answer · 2018-01-25 11:51:12Z

4

The direct translation of that query is to use generate_series() and nextval()

select nextval('id_seq')
from generate_series(1,3);

This will advance the sequence three times.

If however the goal is to set a specific value for an existing sequence (which requires such a hack in Oracle), just use setval():

select setval('id_seq', 3);

answered Jan 25, 2018 at 11:51

user330315

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

1 Answer 1