Insert array as new rows in postgresql without a loop

Question

I have a function that takes 3 values:

partitionId varchar(48)='tenant';
jobId varchar(40)='job1';
taskIds varchar[] := '{job1.1, job1.5, job1.3}';

I would like to have those inserted in a table as:

tenant  job1    job1.1  2020-10-09 11:38:03
tenant  job1    job1.5  2020-10-09 11:38:03
tenant  job1    job1.3  2020-10-09 11:38:03

I know that I could use a loop on the array then insert one by one, but I believe it's not the most efficient so, I am trying to insert in one go. Not sure if I should use an intermediary table for that or if there's a more direct solution.

I tried:

DO
$do$
DECLARE
  
    partitionId varchar(48)='tenant';
    jobId varchar(40)='job1';
    taskIds varchar[] := '{job1.1, job1.5, job1.3}';
begin
    
    insert into completed_subtask_report values (partitionId, jobId, taskIds, current_timestamp );
    
END
$do$

But that would insert the array as:

tenant  job1    {job1.1,job1.5,job1.3}  2020-10-09 12:44:09

Anyone knows how to do that?

If you have received a satisfactory answer please accept it. Please do not leave answered questions as unanswered. — Belayer
– Belayer, Commented Oct 12, 2020 at 3:23

user330315 · Accepted Answer · 2020-10-09 11:59:41Z

3

Use unnest:

insert into completed_subtask_report ( ... column names go here ...)
select 'tenant', 'job1', x.task, current_timestamp
from unnest('{job1.1, job1.5, job1.3}') as x(task)

answered Oct 9, 2020 at 11:59

user330315

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

Collectives™ on Stack Overflow

Insert array as new rows in postgresql without a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related