CODEWITHSUNDEEP
sqlarrayspostgresql
Jane
Jane

Reputation: 269

insert values from equal arrays using WITH PostgreSQL

I would like smth like:

INSERT INTO Transit (idProc, uuidSeg, number) 
select * from 
unnest(array[2, 2]), 
unnest(array ['3ec172b9-b99f-43e2-83bb-527e9b0fb308', '6a72c69c-1083-4c63-83ec-22b0ab512789']::uuid[]), 
unnest(array[1, 2]) where 'some_value' IS NOT NULL;

But this query inserts too many rows:

enter image description here

Inserts correct when I run

WITH data (idProc, uuidSeg, number)  as
    (VALUES
      (2, '3ec172b9-b99f-43e2-83bb-527e9b0fb308'::uuid, 1),
      (2, '6a72c69c-1083-4c63-83ec-22b0ab512789'::uuid, 2))
INSERT INTO Transit (idProc, uuidSeg, number)
SELECT d.idProc, d.uuidSeg, d.number
FROM data d
WHERE 'some_value' is not null;

enter image description here

My question is, What way I can rewrite my first query for it would be possible to insert correctly from arrays simultaneously (they are always equal)? In fact, first column (idProc) is not an array, it is a value, that must be inserted so many times as the length of array. Is it possible?

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

unnest() can take multiple arguments, unnesting them in parallel:

select *  
from unnest(array[2, 2], 
            array ['3ec172b9-b99f-43e2-83bb-527e9b0fb308', '6a72c69c-1083-4c63-83ec-22b0ab512789']::uuid[], 
            array[1, 2]
           ) u(idProc, uuidSeg, number)
where 'some_value' IS NOT NULL;

Upvotes: 3

Related Questions