Upsert in SQL: what is an upsert, and when should you use one?

Upsert in SQL is a database operation that combines updating existing records and inserting new ones, depending on whether a specified value already exists in a table. Although the term "upsert" might not appear in all database management systems (DBMS) documentation, it is a crucial operation for managing data efficiently. Different relational database management systems (RDBMS) such as MySQL, PostgreSQL, and CockroachDB handle upserts with varying syntax and commands. In MySQL, upserts are performed using the "INSERT ... ON DUPLICATE KEY UPDATE" command, while PostgreSQL uses "INSERT ON CONFLICT" to manage conflicts with more control over the operation. CockroachDB offers a dedicated UPSERT command, which simplifies the process by not requiring a conflict target or action, although it can also utilize "INSERT ON CONFLICT" for specific scenarios. Each method has its own advantages and use cases, and understanding these can help optimize database operations and handle data conflicts effectively.