CODEWITHSUNDEEP
sqldatabasepsql
Arnav Malviya
Arnav Malviya

Reputation: 139

Insert numbers into a column PSQL

I have a table called Nums in which I have just one column called text. I need to create a PSQL query that will insert a row for each number between 100000 and 199999.

Row number is 100000
Row number is 100001
Row number is 100002
...
Row number is 199999

Obviously, if the range was smaller and only 10 numbers, it could be done with 10 simple insert statements, but this is not the case. I'm new to PSQL and want to know how this could be achived? Is a sort of loop needed?

Upvotes: 0

Views: 1122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Assuming you mean "Postgres" for the database, use generate_series():

insert into nums (text)
    select n
    from generate_series(100000, 199999, 1) gs(n);

Postgres will automatically convert n to a string -- although it seems unusual to store numbers as strings. You can also be explicit using select n::text.

EDIT:

If you need the full string, it would just look like:

insert into nums (text)
    select 'Row number is ' || (n::text)
    from generate_series(100000, 199999, 1) gs(n);

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

You can use recursive queries.

with recursive cte as
(
  select 100000 as n
  union all
  select   n + 1
  from     cte 
  where    n < 199999 
)
select  * from cte;

Try this dbfiddle

Upvotes: 0

Related Questions