I am confused at the behavior of a peewee insert_many on_conflict query. While I thought I had designated to preserve 'created_at' column and update the 'updated_at' column on conflict, the opposite is occurring.

Upon reviewing the database, 'created_at' is newer than 'updated_at'. I have reviewed the docs for on_conflict. Dialect is SQLite. What is wrong with this query?

query = (
            CA.insert_many(data)
            .on_conflict(
                conflict_target=[CA.loc_id,],
                preserve=(
                    CA.score,
                    CA.tags,
                    CA.process_at,
                    CA.created_at,
                    CA.deleted_at,
                )
            )
        ).execute()

Edit

Based on @coleifer answer (Thank you for the quick response) I misunderstood the meaning of 'preserve' assuming it meant to preserve columns in the database, not the incoming data.

To qualify my original question, the goal is to define the columns to remain unchanged, especially when there may be few columns in the database to remain unchanged and there are many columns. It seems like EXCLUDED might allow this? I wish I had a few more examples.

CodePudding user response：

You don't want to preserve the created_at (this preserves the new value that you are inserting). Preserve means "use the value we would have inserted".

So you only want to preserve the fields you wish to be updated:

query = (CA.insert_many(data)
     .on_conflict(
          conflict_target=[CA.loc_id,],
          preserve=(
              CA.score,
              CA.tags,
              CA.process_at,
              CA.updated_at,
          )
        )
    ).execute()