insert values from equal arrays using WITH PostgreSQL

Question

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:

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;

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?

WHERE 'some_value' is not null; is always true: 'some_value' is a string literal. — wildplasser
– wildplasser, Commented Dec 21, 2019 at 14:00
'some_value' I mean parametric value from Python script (%s) — Jane
– Jane, Commented Dec 23, 2019 at 6:26

Gordon Linoff · Accepted Answer · 2019-12-21 12:33:47Z

3

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;

answered Dec 21, 2019 at 12:33

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

insert values from equal arrays using WITH PostgreSQL

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