Using loop to insert array type of data in PostgreSQL

Question

I'd like to insert array data equip to a table using loop.

var svr = 1;
var equip = [3, 4, 5];

For that I need to insert the data three times.

Looks like this:

INSERT INTO khnp.link_server_equipment_map(svr, equip)
VALUES (1, 3);

INSERT INTO khnp.link_server_equipment_map(svr, equip)
VALUES (1, 4);

INSERT INTO khnp.link_server_equipment_map(svr, equip)
VALUES (1, 5);

Can someone please get me out of this rabbit hole? Thanks

GMB · Accepted Answer · 2020-12-10 09:15:46Z

2

You can try unnest:

INSERT INTO khnp.link_server_equipment_map(svr, equip) 
VALUES (1,  UNNEST(ARRAY[3, 4, 5]));`

edited Dec 10, 2020 at 9:15

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Dec 10, 2020 at 8:52

Bilal Aslam

8397 silver badges16 bronze badges

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

Comments

saloua · Accepted Answer · 2020-12-10 09:09:17Z

0

You can use the INSERT statement to insert several rows.

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

According to your mentioned data example the rows insertion would be done this way

   INSERT INTO khnp.link_server_equipment_map(svr, equip) VALUES
     (1, 3),
     (1, 4),
     (1, 5);

Also to avoid adding the array content one by one you can use the UNNEST array function.

edited Dec 10, 2020 at 9:09

answered Dec 10, 2020 at 8:41

saloua

2,5234 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

Using loop to insert array type of data in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related