0

I have a single table with a single column called id as shown below. I need to populate the table with numbers 1-1000, how can I do that with the scripting?

enter image description here

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
ninchicken
  • 99
  • 1
  • 8

1 Answers1

1

Use generate_series to this:

--Without column declaration
INSERT INTO "MyTable" SELECT generate_series(1, 1000);

--With column declaration
INSERT INTO "MyTable"(id) SELECT generate_series(1, 1000);
Michel Milezzi
  • 10,087
  • 3
  • 21
  • 36
  • Awesome, thanks that works. Just for my own future knowledge, why did you not have to specify the column it is to be populated under? For example, the image that I posted has "id" as the column. – ninchicken Jun 30 '17 at 16:01
  • You can ommit column declaration when *inserted values* has same number of columns of your table. – Michel Milezzi Jun 30 '17 at 16:04
  • BTW, good new article about `generate_series()`: https://regilero.github.io/postgresql/english/2017/06/26/postgresql_advanced_generate_series/ – Nick Jun 30 '17 at 16:06
  • Very good article indeed. Thanks @Nick – Michel Milezzi Jun 30 '17 at 16:09
  • @michel.milezzi: Actually, you *can* omit target column names even with fewer values than columns in the table. Columns are filled in declared order. Details: https://stackoverflow.com/a/11885152/939860 or https://stackoverflow.com/a/9714052/939860. Only advisable for ad hoc use. It would be a trap in persisted statements. Table definitions can change ... – Erwin Brandstetter Jun 30 '17 at 17:38
  • @ErwinBrandstetter Well, I did not ever realized that. Thanks. – Michel Milezzi Jun 30 '17 at 17:53