6

Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id). I have used:

INSERT INTO bins (id)
VALUES (1)

to create a single row with id '1'

How do I create 10000 rows with corresponding id number?

Version: PostgreSQL 9.5.5

11

You can use the generate_series() set returning function:

INSERT INTO bins (id) 
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;
  • Took your advice and read basic SQL, went with - UPDATE bins SET type = 'rubbish' WHERE id BETWEEN 1 AND 1000 (slowly getting fundamentals in place) – Andrew Nov 3 '17 at 23:16
2

With PostgreSQL's GENERATE_SERIES() function, this is a doddle! These functions are very powerful and well worth exploring!

CREATE TABLE bins  AS 
SELECT * FROM GENERATE_SERIES(1, 10) AS id;

SELECT 10
Time: 294.351 ms

Then:

test=# select * from bins;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

Time: 59.766 ms
test=# 

and:

test=# \d bins;
                Table "public.bins"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

test=# 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.