Having trouble with Postgres unnest array syntax

Question

I am looking for guidance on the best way to do this insert. I am trying to create 11 entries for role_id 58385 while looping through the values of each of these arrays. I am new to PostgreSQL and need some guidance as to what I am doing wrong in this instance.

INSERT INTO public.acls (role_id, acl_id, update, can_grant, retrieve, create, archive) VALUES (
           '58385', 
           unnest(array[1,14,20,21,22,24,25,26,36,300,302]), 
           unnest(array[f,f,t,t,f,f,f,t,f,t,t]), 
           unnest(array[f,f,f,f,f,f,f,f,f,f,f]), 
           unnest(array[t,t,t,t,t,t,t,t,t,t,t]), 
           unnest(array[f,f,t,t,f,f,f,t,f,t,t]), 
           unnest(array[f,f,f,f,f,f,f,f,f,f,f])
           )

Do I need a SELECT subquery for each of the arrays? Or could I make one array from the six and Insert them.

I am curious. Why this complicated syntax instead of simple values() clause with constant values? — user330315
– user330315, Commented Aug 11, 2020 at 18:51

Mike Organek · Accepted Answer · 2020-08-11 18:31:58Z

A single select will do it for you, but t and f will need to be true and false:

select '58385',
           unnest(array[1,14,20,21,22,24,25,26,36,300,302]),
           unnest(array[false,false,true,true,false,false,false,true,false,true,true]),
           unnest(array[false,false,false,false,false,false,false,false,false,false,false]),
           unnest(array[true,true,true,true,true,true,true,true,true,true,true]),
           unnest(array[false,false,true,true,false,false,false,true,false,true,true]),
           unnest(array[false,false,false,false,false,false,false,false,false,false,false])
;
 ?column? | unnest | unnest | unnest | unnest | unnest | unnest 
----------+--------+--------+--------+--------+--------+--------
 58385    |      1 | f      | f      | t      | f      | f
 58385    |     14 | f      | f      | t      | f      | f
 58385    |     20 | t      | f      | t      | t      | f
 58385    |     21 | t      | f      | t      | t      | f
 58385    |     22 | f      | f      | t      | f      | f
 58385    |     24 | f      | f      | t      | f      | f
 58385    |     25 | f      | f      | t      | f      | f
 58385    |     26 | t      | f      | t      | t      | f
 58385    |     36 | f      | f      | t      | f      | f
 58385    |    300 | t      | f      | t      | t      | f
 58385    |    302 | t      | f      | t      | t      | f
(11 rows)

Collectives™ on Stack Overflow

Having trouble with Postgres unnest array syntax

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