SQL: insert multiple rows at once

Question

I have an array of values like this (restaurant_id is not a primary key):

[
{restaurant_id:1, day_of_week:0, from_time: "12:00", to_time: "14:00", is_open:false },
{restaurant_id:1, day_of_week:1, from_time: "12:00", to_time: "14:00", is_open:true },
{restaurant_id:1, day_of_week:2, from_time: "12:00", to_time: "14:00", is_open:true },
...
]

One entry for every day.

I would like to save each of them as a new row in a PostgreSQL database.

I have this query for one insert:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open) VALUES ($1, $2, $3, $4, $5) RETURNING schedules;

Should I do 7 INSERT statements or can I loop and save all in one statement?

What would be the query with the loop?

EDIT:

So I could do in one query something like this, as suggested:

 VALUES (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?)

but is there a better way of doing this?

i assume you are using some kind of programming lauguage also as you have mentioned *"What would be the query with the loop?"* — Raymond Nijland, Jun 15 '19 at 12:52

score 4 · Answer 1 · edited Jun 15 '19 at 13:41

4

If all other values are constant(or derivable from the running variable), you can use generate_series()

INSERT INTO schedules (restaurant_id, day_of_week
           , from_time, to_time, is_open) 
SELECT 1, gs, '10:00','22:00', True
FROM generate_series(0,6) gs
        ;

Docs for generate_series https://www.postgresql.org/docs/11/functions-srf.html

edited Jun 15 '19 at 13:41

Eyeslandic

14,553
13
41
54

answered Jun 15 '19 at 12:42

wildplasser

43,142
8
66
109

Is that a postgres specific function? – Eyeslandic Jun 15 '19 at 13:38
1

Yes. Generate_series() is a postgres extension. – wildplasser Jun 15 '19 at 13:44

score 2 · Answer 2 · answered Jun 15 '19 at 12:05

2

You can issue one insert. I would recommend using parameters:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open)
    VALUES (?, ?, ?, ? ?),
           (?, ?, ?, ? ?),
           . . .
    RETURNING *;

answered Jun 15 '19 at 12:05

Gordon Linoff

1,242,037
58
646
786

Ah yes! So better to explicitly setting VALUES for each row, than looping through the array? – Kevin Amiranoff Jun 15 '19 at 12:08

score 2 · Answer 3 · answered Jun 15 '19 at 12:12

2

INSERT INTO USERS(id,name,age) VALUES (1, 'Talha', 22), (2, 'John', 41), (3, 'William', 32);

This will work i guess.

answered Jun 15 '19 at 12:12

Talha Asif

61
7

Yes, I just wanted to know if I could loop and avoid writing `(1, 'Talha', 22),` 7 times... – Kevin Amiranoff Jun 15 '19 at 12:14
Kevin Amiranoff, You can use stored procedures of mysql for that. begin for i in 1 .. 100000 loop insert into users values ( i, 'Talha' ,20+i); end loop; commit; end; – Talha Asif Jun 15 '19 at 12:44

score 2 · Accepted Answer · answered Jun 15 '19 at 13:48

If those input values are actually part of a JSON array, you can use that directly:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open) 
select (v ->> 'restaurant_id')::int, 
       (v ->> 'day_of_week')::int,
       (v ->> 'from_time')::time,
       (v ->> 'to_time')::time,
       (v ->> 'is_open')::boolean
from jsonb_array_elements('
[
  {"restaurant_id":1, "day_of_week":0, "from_time": "12:00", "to_time": "14:00", "is_open":"false" },
  {"restaurant_id":1, "day_of_week":1, "from_time": "12:00", "to_time": "14:00", "is_open":"true" },
  {"restaurant_id":1, "day_of_week":2, "from_time": "12:00", "to_time": "14:00", "is_open":"true" }
]'::jsonb) as t(v);

Of course you need to replace the hardcoded string value with a proper parameter, e.g. from jsonb_array_elements(cast(? as jsonb))

score -1 · Answer 5 · answered Jun 15 '19 at 12:45

-1

Kevin Amiranoff, You can use stored procedures of mysql for that.

begin for i in 1 .. 100000 loop insert into users values ( i, 'Talha' ,20+i); end loop; commit; end;

answered Jun 15 '19 at 12:45

Talha Asif

61
7

https://stackoverflow.com/questions/19145761/postgres-for-loop refer to this link , it might help – Talha Asif Jun 15 '19 at 12:55

SQL: insert multiple rows at once

5 Answers5