CODEWITHSUNDEEP
arrayspostgresql
Xbreizh
Xbreizh

Reputation: 373

Insert array as new rows in postgresql without a loop

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?

Upvotes: 1

Views: 269

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 2

Related Questions