Sql

Introduction Both SQLite and PostgreSQL support UPSERT operations, which means “update if exists, insert if not.” The conflict column must have a unique constraint. Syntax: PostgreSQL: INSERT ... ON CONFLICT (column) DO UPDATE/NOTHING SQLite: INSERT ... ON CONFLICT(column) DO UPDATE/NOTHING (note the parentheses position) Scenario PostgreSQL SQLite Notes Basic UPSERT ON CONFLICT (col) DO UPDATE SET ... ON CONFLICT(col) DO UPDATE SET ... Slight difference in parentheses placement Conflict Ignore ON CONFLICT (col) DO NOTHING ON CONFLICT(col) DO NOTHING Same syntax Reference New Values EXCLUDED.col excluded.col PostgreSQL uses uppercase, SQLite uses lowercase Return Results RETURNING * RETURNING * Same syntax Conditional Update WHERE condition WHERE not supported SQLite limitation Key Considerations The conflict column must have a unique constraint PostgreSQL and SQLite syntax is similar but has subtle differences. Pay attention when using raw SQL. SQLite does not support WHERE clauses in UPSERT operations; use CASE expressions or application-level filtering instead. SQLite version 3.35+ is required for RETURNING support. EXCLUDED and RETURNING EXCLUDED EXCLUDED represents the new values that were intercepted due to a conflict. ...