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
– Raymond Nijland, Commented Jun 15, 2019 at 12:52

Eyeslandic · Accepted Answer · 2019-06-15 13:41:39Z

5

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, 2019 at 13:41

Eyeslandic

14.9k13 gold badges44 silver badges55 bronze badges

answered Jun 15, 2019 at 12:42

wildplasser

44.4k9 gold badges72 silver badges116 bronze badges

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

2 Comments

Eyeslandic Over a year ago

Is that a postgres specific function?

wildplasser Over a year ago

Yes. Generate_series() is a postgres extension.

user330315 · Accepted Answer · 2019-06-15 13:48:10Z

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))

Gordon Linoff · Accepted Answer · 2019-06-15 12:05:24Z

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, 2019 at 12:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Kevin Amiranoff Over a year ago

Ah yes! So better to explicitly setting VALUES for each row, than looping through the array?

Talha Asif · Accepted Answer · 2019-06-15 12:12:45Z

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, 2019 at 12:12

Talha Asif

718 bronze badges

2 Comments

Kevin Amiranoff Over a year ago

Yes, I just wanted to know if I could loop and avoid writing (1, 'Talha', 22), 7 times...

Talha Asif Over a year ago

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 · Accepted Answer · 2019-06-15 12:45:15Z

-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, 2019 at 12:45

Talha Asif

718 bronze badges

1 Comment

Talha Asif Over a year ago

stackoverflow.com/questions/19145761/postgres-for-loop refer to this link , it might help

Collectives™ on Stack Overflow

SQL: insert multiple rows at once

5 Answers 5

2 Comments

Comments

1 Comment

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

2 Comments

1 Comment

Linked

Related