0

I was wondering why, when using Peewee at least, UPSERT does DELETE and INSERT rather than UPDATE when the entry is already in the table?

Is there any reason for which it would be preferable to "DELETE then INSERT" rather than "try UPDATE otherwise INSERT"?

Is UPDATE more time-expensive than DELETE & INSERT? Or is UPSERT really meant as an INSERT(force=True) query?

bsuire
  • 1,383
  • 2
  • 18
  • 27
  • 1
    I don't think SQLite supports UPSERT.. there's no documentation about it: https://www.sqlite.org/docs.html – BeNdErR Mar 23 '15 at 16:18
  • my bad. Apparently that layer is handled by Peewee. Juste edited the question. However, from what I've read it seems like a common implementation. – bsuire Mar 23 '15 at 16:32
  • It's possible that the data in the update takes up more space and can't be updated in-place. – D Stanley Mar 23 '15 at 17:34
  • @BeNdErR, SQLite does support UPSERT via `INSERT OR REPLACE INTO`. @bsuire, peewee will not "fake" an upsert. If the database supports it, a native upsert will be issued, otherwise you will receive an error. – coleifer Mar 26 '15 at 20:48

2 Answers2

1

I was wondering why, when using Peewee at least, UPSERT does DELETE and INSERT rather than UPDATE when the entry is already in the table?

Peewee does not explicitly do a delete then insert. If you are using SQLite, which does support upsert, then peewee will issue:

INSERT OR REPLACE INTO <table> ...

This is how peewee implements upsert. Peewee itself will not issue a separate DELETE statement.

See https://www.sqlite.org/lang_replace.html

coleifer
  • 24,887
  • 6
  • 60
  • 75
1

isn't this better:

UPDATE t SET a = 'pdf' WHERE id = 2;
INSERT INTO t(id, a) SELECT 2, 'pdf' WHERE changes() = 0;

If the update fails then changes()=0 so it does an insert.

AnthonyLambert
  • 8,768
  • 4
  • 37
  • 72