Inserting multiple rows from a SELECT statement along with an array in another column

Question

Hello when I run the below query:

SELECT fundid 
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wx

I get:

"fundid"
11111
22222

So now what I'm trying to do is insert both those fundid rows along with an array so that the data will look something like below:

"fundid" | "listdate"
11111    | {2021-01-02, 2021-01-03}
22222    | {2021-01-02, 2021-01-03}

I've tried to finagle the query's around to get an insert working such as -

INSERT into nav_due_dates (fund_id, listdate)
VALUES(
        (
            SELECT fundid 
            FROM funds
            WHERE reportingfrequency = 'Daily'
            AND primarynavcostlocation = 'Wroclaw'
            AND primaryaccountmgr1 = 'wxy'
        ),
        (
            SELECT array_agg(weekdays::date)
            FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
            LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
            WHERE f.datestamp IS NULL 
            AND extract(dow from weekdays) BETWEEN 1 AND 5
        )
);

It returns:

ERROR: more than one row returned by a subquery used as an expression

and I've also tried the following after doing some digging -

INSERT INTO nav_due_dates (fund_id, listdate)
        SELECT fundid 
        WHERE reportingfrequency = 'Daily'
        AND primarynavcostlocation = 'Wroclaw'
        AND primaryaccountmgr1 = 'wxy', 
             (
                SELECT array_agg(weekdays::date)
                FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
                LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
                WHERE f.datestamp IS NULL 
                AND extract(dow from weekdays) BETWEEN 1 AND 5
             )
       FROM funds;

But the syntax errors I can't seem to resolve such as removing the , or the parenthesis around the array select statement that populates the listdate column.

eshirvana · Accepted Answer · 2021-04-13 19:26:28Z

1

I think you want to do this :

insert into nav_due_dates (fund_id, listdate)
select fundid, dd 
from 
    (
        SELECT fundid 
        FROM funds
        WHERE reportingfrequency = 'Daily'
        AND primarynavcostlocation = 'Wroclaw'
        AND primaryaccountmgr1 = 'wxy'
    ) t1
cross join 
    (
        SELECT array_agg(weekdays::date) dd
        FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
        LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
        WHERE f.datestamp IS NULL 
        AND extract(dow from weekdays) BETWEEN 1 AND 5
    ) t2

answered Apr 13, 2021 at 19:26

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

animusdx Over a year ago

Thanks for the answer, but I managed to solve it on my own and it was just an ordering on the syntax issue XD

animusdx · Accepted Answer · 2021-04-13 19:48:19Z

0

So I managed to get it working and it was entirely because of the ordering of the FROM section.

INSERT INTO nav_due_dates (fund_id, listdate)
SELECT fundid,
     (
        SELECT array_agg(weekdays::date)
        FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
        LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
        WHERE f.datestamp IS NULL 
        AND extract(dow from weekdays) BETWEEN 1 AND 5
     )
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wxy';

answered Apr 13, 2021 at 19:48

animusdx

4206 silver badges18 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows from a SELECT statement along with an array in another column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related