Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgresql bulk upsert in Python (Django)
def bulk_upsert(model, fields, values, by):
"""
Return the tuple of (inserted, updated) ids
"""
result = (None, None)
if values:
stmt = """
WITH data_set AS (
INSERT INTO %s (%s)
SELECT DISTINCT ON (%s) *
FROM (
VALUES %s
) v(%s)
ON CONFLICT (%s)
DO
UPDATE SET (%s)=(%s)
RETURNING xmax, id
)
SELECT ARRAY_AGG(id) FILTER (WHERE xmax = 0) AS ins,
ARRAY_AGG(id) FILTER (WHERE xmax::text::int > 0) AS upd
FROM data_set;
"""
table_name = model._meta.db_table
set_fields = ', '.join([f for f in fields if f != by])
set_values = ', '.join(['EXCLUDED.{0}'.format(f) for f in fields if f != by])
fields_str = ', '.join(fields)
values_placeholders = ('%s, ' * len(values))[:-2]
formatted_sql = stmt % (
table_name,
fields_str,
by,
values_placeholders,
fields_str,
by,
set_fields,
set_values
)
with connection.cursor() as cursor:
cursor.execute(formatted_sql, values)
result = cursor.fetchall()[0]
return result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment