Instantly share code, notes, and snippets.

Embed
What would you like to do?
Bulk upsert (Rails/PostgreSQL)
class Data < ApplicationRecord
class << self
def bulk_upsert(values)
transaction do
values.uniq { |v| v[:key] }.each_slice(1000) do |values_slice|
connection.exec_query <<-SQL.squish
INSERT INTO #{table_name}(#{upsert_columns.join(', ')})
VALUES #{upsert_values_statement(values_slice)}
ON CONFLICT(key)
DO UPDATE SET
payload = EXCLUDED.payload,
expires_at = EXCLUDED.expires_at,
updated_at = CURRENT_TIMESTAMP
SQL
end
end
end
private
def upsert_columns
%w[key payload expires_at]
end
def upsert_values_statement(values)
values.map do |item|
<<-SQL.squish
(
#{quote_value('key', item.key)},
#{quote_value('payload', item.payload)},
#{quote_value('expires_at', item.expires_at || nil)}
)
SQL
end.join(', ')
end
def quote_value(name, value)
type_cast = connection.type_cast_from_column(columns_hash[name], value)
connection.quote(type_cast)
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment