Skip to content

Instantly share code, notes, and snippets.

@aisayko
Last active May 8, 2022 23:53
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save aisayko/dcacd546bcb17a740dec703de6b2377e to your computer and use it in GitHub Desktop.
Save aisayko/dcacd546bcb17a740dec703de6b2377e to your computer and use it in GitHub Desktop.
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
@yotamss
Copy link

yotamss commented Jul 12, 2021

Any reason for "xmax::text::int > 0 instead of "xmax > 0"?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment