postgresqlsql-insertgenerate-series

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

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.

Solution

  • You can treat this just like a normal INSERT SELECT

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