upsert shortcut for postgresql

[Elypha]

hello,

I think something like on_conflict_replace is not supported by postgresql (instead we use on_conflict) but I wonder if it's possible to have a peewee shortcut for the use case of a full replace, for example

# original
User.insert(
    id=id,
    username=username,
    value1=value1,
).on_conflict(
    conflict_target=[User.id],
    update={
        User.username : username,
        User.value1 : value1,
    }
).execute()

# proposed shortcut1
User.insert(
    id=id,
    username=username,
).on_conflict(
    conflict_target=[User.id],
    update_all = True,
).execute()

# proposed shortcut2
User.insert(
    id=id,
    username=username,
).on_conflict_replace().execute()

the problem is that, using the postgre on_conflict we need to specify each column (I think?), and when upsert a wide table it could be a bit painful for writing and editing

User.insert(
    id=id,
    username=username,
    value1=value1,
    value2=value2,
    ...
    ...
    value20=value20,
).on_conflict(
...

I assume replace-all can be a usual case so maybe we can have a shortcut to save sanity. what do you think of this?

[coleifer]

I don't plan to add this without giving it more thought, and I'm inclined not to as the Postgres API is a bit finnicky. My suggestion would be to wrap your desired usage in a helper, e.g.:

def replace(ModelClass, conflict_target, **data):
    iq = ModelClass.insert(**data)
    return iq.on_conflict(
        conflict_target=conflict_target,
        preserve=[k for k in iq._insert])

class Reg(db.Model):
    key = CharField(unique=True)
    value = TextField()
    extra = IntegerField()

replace(Reg, [Reg.key], key='k1', value='v1', extra=1).execute()
replace(Reg, [Reg.key], key='k2', value='v2', extra=2).execute()
replace(Reg, [Reg.key], key='k1', value='v1-x', extra=10).execute()
replace(Reg, [Reg.key], key='k1', value='v1-y', extra=100).execute()

for row in Reg:
    print(row.id, row.key, row.value, row.extra)

# Output:
# 2 k2 v2 2
# 1 k1 v1-y 100

[farrantch]

I would also love this feature!