Trying to re implement this oracle bulk insert to postgres equivalent.
Currently have:
a.each do |b|
params << [nil, b.value]
inserts << %{INSERT INTO table(a, b, c, d)
VALUES (:a1, :a2, :a3, :a4); }
end
sql = inserts.join
ActiveRecord::Base.transaction do
# insert
ActiveRecord::Base.connection.exec_update(sql, 'table', params)
end
What would a similar implementation using activerecord / postgres look like?
I am getting the below error
ActiveRecord::StatementInvalid:
# ERROR: syntax error at or near ":"
# LINE 4: VALUES (:a1, :a2, :a3, :a4, :a5, :a6, :a7, :...
# ^
Postgresql uses the syntax $1, $2, etc for parameter placeholders. Replace the Oracle :a1, :a2, ... with $1, $2, ...
SQL has a concept of prepared statements, where you supply the statement with placeholders instead of actual values. You can then execute the statement one or more times supplying the parameters each time. This is more efficient because the server only has to parse, analyze and plan your query once.
In answer to your comment, it looks like you are preparing a string containing multiple INSERT statements concatenated together. Instead you need to prepare only one INSERT statement and then execute it multiple times, once for each row of parameters.
Unfortunately I don't know enough about ActiveRecord or even Ruby to be able to suggest how to rewrite your code to achieve that!
By the way, this is not what I would call a 'bulk insert'. The fastest way to get data into postgresql is usually via the COPY command.