SQL: Problems translating recursive connect by Statement for Postgres

Question

I try to translate the following oracle sql, which inserts 1000 rows with incremental values into a table:

insert into tableName (col1, col2, col3)
    select 'AN' || (1000000 + ROWNUM), 'EXT' || (9000000 + ROWNUM), ROWNUM
    from dual
    Connect By  ROWNUM <= 1000 ;

For Postgres support, i know i can substitute ROWNUM with ROW_NUMBER() OVER (), but i'm really getting a headache about translating the connect by statement. I have read about CTEs but i don't get how i can use this with an insert statement.

Does anyone know how to write this statement for postgresql? Thanks.

Gordon Linoff · Accepted Answer · 2018-04-27 11:52:33Z

2

You can generate a series and just use that:

insert into tableName (col1, col2, col3)
    select 'AN' || (1000000 + g.n), 'EXT' || (9000000 + g.n), g.n
    from generate_series(1, 1000) g(n);

answered Apr 27, 2018 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2018-04-27 11:52:11Z

1

Try generate_series.

 select 'AN' || (1000000 + ROWNUM), 'EXT' || (9000000 + ROWNUM),
 ROWNUM from generate_series(1,10000) as rownum ;

answered Apr 27, 2018 at 11:52

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

SQL: Problems translating recursive connect by Statement for Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related