CODEWITHSUNDEEP
sqlpostgresql
Kash
Kash

Reputation: 361

Generate table with row numbers postgres sql

I would like to create a temp table with just row numbers generated. I do not have any source table to pull data from.

For example, I need to create a column named index with 1-50 rows.

Upvotes: 1

Views: 818

Answers (2)

The Impaler
The Impaler

Reputation: 48800

You can use a recursive CTE:

create table t (id int);

with recursive
x (n) as (
  select 1
 union all
  select n + 1 from x where n < 50
)
insert into t (id)
select n from x;

See running example at DB Fiddle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Use generate_series(). But don't use the name index because that is a SQL keyword so it is a very bad choice for a column name:

select gs.i
from generate_series(1, 50, 1) gs(i);

Upvotes: 5

Related Questions