artofhuman / django-pg-upsert

Support Postgres native upsert (INSERT ... ON CONFLICT) for django

django-pg-upsert

Support Postgres native upsert (INSERT ... ON CONFLICT) for django

Installation

pip install django-pg-upsert

Usage

As a manager

import django_pg_upsert

from django.db import models

class Pet(models.Model):
    name = models.CharField(max_length=30, unique=True)
    age = models.PositiveIntegerField()

    objects = PgUpsertManager()


Pet.objects.insert_conflict(data={"name": "dog", "age": 12})

# second query don't insert record to db and don't raise error
Pet.objects.insert_conflict(data={"name": "dog", "age": 20})

# This code produce SQL statement

[
  'INSERT INTO "pets" ("name", "age") VALUES (%s, %s) ON CONFLICT DO NOTHING',
  ("dog", 12),
]

Explicit constraint name

Pet.objects.insert_conflict(
  data={"name": "dog", "age": 12},
  constraint="pet_name_uniq"
)

[
  'INSERT INTO "pets" ("name", "age") VALUES (%s, %s) ON CONSTRAINT pet_name_uniq DO NOTHING',
  ("dog", 12),
]

Using field names

Pet.objects.insert_conflict(
  data={"name": "dog", "age": 12},
  fields=["name"]
)

[
  'INSERT INTO "pets" ("name", "age") VALUES (%s, %s) ON CONSTRAINT ("name") DO NOTHING',
  ("dog", 12),
]

As a standalone function

import django_pg_upsert

pet = Pet(name='dog', age='12')

django_pg_upsert.insert_conflict(pet)
django_pg_upsert.insert_conflict(pet, constraint='pet_name_uniq')
django_pg_upsert.insert_conflict(pet, fields='name')

Update

Pet.objects.insert_conflict(
  data={"name": "dog", "age": 100},
  fields=["name"],
  update=["age"]
)

or

django_pg_upsert.insert_conflict(pet, fields='name', update=["age"])

[
  'INSERT INTO "pets" ("name", "age") VALUES (%s, %s) ON CONFLICT ("name") DO UPDATE SET age = EXCLUDED.age',
  ("dog", 100),
]

Motivation

django-postgres-extra has a pg upsert method as well, but this package requires redefinition for DB backed in Django settings which sometimes is not possible.

django-pg-upsert is designed to solve only one problem (depends only from django) and is not a Swiss knife.

django-pg-upsert's Issues

Django 2.x and Python 3.9 Support

Hi, This package seems like it used to support 2.x versions. Then you updated the required version to 3.x versions of Django. Is there a way that you can support both 2.x and 3.x versions with something like this? >= 2.0, < 4.0. From what I understand Poetry's caret requirements does not support multiple major versions. Same goes for Python, this package should be able to run on Python 3.7 3.8 and 3.9 (and probably 3.10)

I'll open an MR as soon as possible.

deinstalls django and installs old versions

I installed pip install django-pg-upsert, but it uninstalled django 4, installed django 3 and installed django-pg-upsert 0.3

Update readme for #5

Add CI

probably github actions or travic ci

Update for Django 3.x?

Could this be updated for Django 3.2?

ps - is djnago_settings.py meant to be misspelled?

build matrix

Support Django 4.0

Add 4.0 to CI build
add/fix tests if needed

Escape column names in IgnoreConflictSuffix._action_sql property

We have a table with a order column. When i try to use this field in a update=['order'] i got an error:

'syntax error at or near "order"
LINE 1: ..."network_id") DO UPDATE SET data = EXCLUDED.data, order = EX...
                                                             ^

Need to wrap column names in a DO UPDATE SET clause with a " double quotes (like a _conflict_sql).
Or mention in documentation that caller need to put double quotes around the fieldname: update=['"order"']

Implement WHERE condition

PG doc: https://www.postgresql.org/docs/10/sql-insert.html

Need to implement part where we can update conflicted rows using condition expression

INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
ON CONFLICT (did) DO UPDATE
SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
WHERE d.zipcode <> '21201';

Possible API will maybe:

django_pg_upsert.insert_conflict(pet, fields='name', update=["age"], update_where="...")

Support on conflict update