Multiple insert in parametrized statement with PG gem

Daniel

New Member
#1
Using the Ruby PG gem, I am trying to insert several rows at the same time with exec_params.

What I would like to acheive is:
Mã:
INSERT INTO public.my_things (col1, col2)
VALUES
    ('value11', 'value12'),
    ('value21', 'value22'),
    ('value31', 'value32');
I tried this (and other combinations):
Mã:
connection.exec_params(
  'INSERT INTO public.my_things (col1, col2) VALUES ($1, $2)',
  [['value11', 'value12'], ['value21', 'value22'], ['value31', 'value32']],
)
 

Admin

Administrator
Thành viên BQT
#2
There are two things to note:

  1. You need the right number of placeholders.
  2. #exec_params wants a flat array for the parameters.
So you want to say:
Mã:
connection.exec_params(
  'INSERT INTO public.my_things (col1, col2) VALUES ($1, $2), ($3, $4), ($5, $6)',
  ['value11', 'value12', 'value21', 'value22', 'value31', 'value32']
)
Or, if you have an array-of-arrays and you don't know how long it will be until runtime:
Mã:
aoa = [['value11', 'value12'], ['value21', 'value22'], ['value31', 'value32'], ... ]
values = aoa.length.times.map { |i| "($#{2*i+1}, $#{2*i+2})" }.join(',')
connection.exec_params(
  "INSERT INTO public.my_things (col1, col2) VALUES #{values}",
  aoa.flatten
)
 
Top