-1

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.

Gokul Velu
  • 31
  • 1
  • 7
  • Possible duplicate of [What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?](https://stackoverflow.com/questions/24898681/what-is-the-equivalent-postgresql-syntax-to-oracles-connect-by-start-with) – Aleksej Jan 25 '18 at 11:49
  • 1
    @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 –  Jan 25 '18 at 11:52
  • @a_horse_with_no_name: my mistake – Aleksej Jan 25 '18 at 11:53
  • 1
    Possible duplicate of [Generate n rows of NULL in PostgreSQL](https://stackoverflow.com/questions/16573945/generate-n-rows-of-null-in-postgresql) – William Robertson Jan 25 '18 at 11:53

1 Answers1

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);