generate_series() - how to use it to populate multiple columns in a table?

Question

I have a table with the following structure:

widgetnumber - text
dd  - text 
refnumber - text

widgetnumber and refnumber need to be populated with the same value... one that's generated using generate_series.
dd will be a static value that's set once.

I have the following code:

INSERT INTO widgets(widgetnum, dd, refnum)
SELECT i
FROM generate_series(100, 150) AS t(i), 
'somestaticstring',
SELECT p
FROM generate_series(100, 150) AS t(p);

But I must have a syntax error somewhere because this is not working. I'm getting the following error:

psql:addwidgets.sql:11: ERROR:  syntax error at or near "'somestaticstring'"
LINE 4: 'somestaticstring',
        ^

What I've tried

I've tried to change the single quotes around the static text to double. I've also tried this:

INSERT INTO widgets(widgetnum, dd, refnum)
SELECT i
FROM generate_series(100, 150) AS t(i), 
SELECT 'somestaticstring',
SELECT p
FROM generate_series(100, 150) AS t(p);

But that also fails with a syntax error.

Why are you storing numbers in `text` columns? – Dec 23 '16 at 17:06 — , Dec 23 '16 at 17:06

score 20 · Answer 1 · answered Dec 23 '16 at 17:06

20

Make it part of the select statement:

INSERT INTO widgets(widgetnum, dd, refnum)
SELECT i::text, 'somestaticstring', i::text
FROM generate_series(100, 150) AS t(i),

answered Dec 23 '16 at 17:06

I am curious, what does the "AS t(i)" bit do exactly ? – Ellert van Koperen Sep 05 '22 at 10:59
1

@EllertvanKoperen: it's a [table alias](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLE-ALIASES) – Sep 05 '22 at 14:52
1

Aha, so it names the generated table t and the single column i, gotcha. Thanks, very helpfull. – Ellert van Koperen Sep 06 '22 at 08:46

score 9 · Accepted Answer · answered Dec 23 '16 at 17:08

9

You can treat this just like a normal INSERT SELECT

INSERT INTO widgets
SELECT generate_series(100,150), 'somestaticstring', generate_series(100,150)

answered Dec 23 '16 at 17:08

e4c5

52,766
11
101
134

4

@Happydevdays: While multiple set-returning functions in the `SELECT` list are allowed in Postgres (unlike standard SQL), it's not a good idea. Surprising behavior if not all return the same number of rows. Move set-returning functions to the `FROM` clause whenever possible. Consider a_horse's answer. – Erwin Brandstetter Dec 23 '16 at 21:49
I prefer the solution from *a_horse*: https://stackoverflow.com/a/41305292/967621 . It avoids repetitive calls to `generate_series`, thus it is more [DRY](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself). This is in addition to the comment from @ErwinBrandstetter. – Timur Shtatland Jun 01 '23 at 15:01

generate_series() - how to use it to populate multiple columns in a table?

2 Answers2