CODEWITHSUNDEEP
sqlpostgresql
flixe
flixe

Reputation: 646

SQL: Problems translating recursive connect by Statement for Postgres

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.

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

Try generate_series.

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

Upvotes: 1

Related Questions