Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

postgresql

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

like image 910
Happydevdays Avatar asked Dec 23 '16 16:12

Happydevdays


2 Answers

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), 
like image 192
a_horse_with_no_name Avatar answered Oct 18 '22 11:10

a_horse_with_no_name


You can treat this just like a normal INSERT SELECT

INSERT INTO widgets
SELECT generate_series(100,150), 'somestaticstring', generate_series(100,150)
like image 41
e4c5 Avatar answered Oct 18 '22 12:10

e4c5