UPSERT w SQLite: ON CONFLICT DO UPDATE i DO NOTHING

Wstaw albo zaktualizuj, jeśli już istnieje

Częsta potrzeba: wstawić wiersz, ale jeśli wiersz z tym samym kluczem już istnieje, zaktualizować go. Bez UPSERT trzeba najpierw wykonać SELECT, a potem rozgałęzić się na INSERT albo UPDATE: dwa odwołania do bazy i wyścig między nimi.

UPSERT w SQLite robi to jedną instrukcją:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products;

Za pierwszym uruchomieniem wiersz zostaje wstawiony. Uruchom to ponownie z inną ceną i tym samym sku, a istniejący wiersz zostanie zaktualizowany na miejscu. Bez duplikatu, bez błędu.

Budowa ON CONFLICT

Pełny schemat:

INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;

Liczą się trzy elementy:

• conflict_target: kolumna lub kolumny z ograniczeniem UNIQUE albo PRIMARY KEY, na których spodziewasz się kolizji. SQLite używa tego, żeby wybrać indeks do obserwowania.
• DO UPDATE SET ...: co zmienić w istniejącym wierszu, gdy dojdzie do kolizji. (Albo DO NOTHING, żeby po cichu pominąć.)
• Opcjonalne WHERE: dodatkowy warunek, który musi być prawdziwy, żeby aktualizacja faktycznie się wykonała.

Cel konfliktu musi odpowiadać prawdziwemu ograniczeniu unikalności. ON CONFLICT(price) się nie skompiluje, jeśli price nie jest unikalne: SQLite nie ma względem czego wykrywać konfliktu.

DO NOTHING: wstaw, jeśli brak, w przeciwnym razie pomiń

Prostszy wariant. Przydaje się, gdy zasilasz bazę danymi albo zapisujesz zdarzenia, a duplikaty mają być po cichu ignorowane:

CREATE TABLE seen_events (
    event_id TEXT PRIMARY KEY,
    seen_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-2')
ON CONFLICT(event_id) DO NOTHING;

SELECT * FROM seen_events;

Drugie wstawienie trafia na ten sam event_id i normalnie zgłosiłoby UNIQUE constraint failed. Z DO NOTHING SQLite po prostu je pomija. Bez wyjątku, bez zmienionych wierszy.

To „idempotentne wstawienie”, do którego ludzie często używają INSERT OR IGNORE. DO NOTHING z UPSERT robi to samo i lepiej współgra z klauzulami WHERE i RETURNING.

Pseudotabela excluded

Gdy dochodzi do konfliktu, nagle w grze są dwa wiersze: istniejący w tabeli i nowy, który próbujesz wstawić. SQLite daje sposób, żeby mówić o obu.

• Same nazwy kolumn (price, name) odnoszą się do istniejącego wiersza.
• excluded.column odnosi się do przychodzącego wiersza, który został odrzucony.

CREATE TABLE inventory (
    sku      TEXT PRIMARY KEY,
    quantity INTEGER NOT NULL,
    updated  TEXT
);

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 5, '2026-04-01')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 3, '2026-04-02')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

SELECT * FROM inventory;

quantity = quantity + excluded.quantity czytaj jako „istniejąca ilość plus nowa”. Po dwóch wstawieniach A-100 ma ilość 8. Ten wzorzec, czyli sumowanie w istniejącym wierszu, to jedna z najbardziej przydatnych sztuczek UPSERT.

Warunkowy UPSERT z WHERE

Końcowe WHERE pozwala pominąć aktualizację, jeśli jakiś warunek nie jest spełniony. Jest sprawdzane względem istniejącego wiersza (i może odwoływać się do excluded.* dla przychodzącego):

CREATE TABLE prices (
    sku        TEXT PRIMARY KEY,
    price      REAL NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO prices VALUES ('A-100', 10.00, '2026-04-01');

INSERT INTO prices (sku, price, updated_at)
VALUES ('A-100', 12.00, '2026-03-15')
ON CONFLICT(sku) DO UPDATE SET
    price      = excluded.price,
    updated_at = excluded.updated_at
WHERE excluded.updated_at > prices.updated_at;

SELECT * FROM prices;

Nowy wiersz ma starsze updated_at, więc WHERE jest fałszywe i aktualizacja zostaje pominięta. Istniejący wiersz zachowuje nowszą cenę. Zamień daty, a aktualizacja się wykona. To standardowy wzorzec „nadpisuj tylko świeższymi danymi”.

Upsert wielu wierszy

VALUES może zawierać wiele wierszy, a ON CONFLICT działa dla każdego z nich niezależnie:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products VALUES ('A-100', 'Widget', 9.99);

INSERT INTO products (sku, name, price) VALUES
    ('A-100', 'Widget v2', 10.99),
    ('A-200', 'Gadget',     14.99),
    ('A-300', 'Gizmo',      19.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A-100 koliduje i zostaje zaktualizowany. A-200 i A-300 są nowe i zostają wstawione. Jedna instrukcja, mieszany wynik: wstawienia i aktualizacje. To czysty sposób na synchronizację paczki rekordów z zewnętrznego źródła.

UPSERT vs INSERT OR REPLACE

INSERT OR REPLACE wygląda, jakby robił to samo. Nie robi.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

-- Replace: usuwa stary wiersz, wstawia nowy.
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

SELECT * FROM users;

notes zniknęło. INSERT OR REPLACE całkowicie usunął wiersz 1 i wstawił nowy: każda kolumna, której nie wymieniono, została ustawiona na NULL albo wartość domyślną. Odpala też wyzwalacze DELETE i uruchamia kaskady przez klucze obce z ON DELETE.

UPSERT zachowuje wiersz:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

INSERT INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT(id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

SELECT * FROM users;

notes wciąż tam jest. Zmieniły się tylko kolumny wymienione w SET. Domyślnie sięgaj po UPSERT; po INSERT OR REPLACE tylko wtedy, gdy naprawdę chcesz semantyki „usuń i wstaw od nowa”.

Wiele celów konfliktu

Jeśli wiersz może kolidować na więcej niż jednym ograniczeniu, możesz połączyć kilka klauzul ON CONFLICT:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email    TEXT UNIQUE,
    score    INTEGER DEFAULT 0
);

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 10)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 5)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

SELECT * FROM accounts;

Wygrywa to ograniczenie, które zadziała pierwsze, i wykonuje się DO UPDATE z tej gałęzi. W praktyce większość tabel ma jeden oczywisty cel konfliktu, czyli klucz główny albo jedną unikalną kolumnę, więc rzadko potrzebujesz więcej niż jednej klauzuli.

Typowe pułapki

Kilka rzeczy, które dają ludziom w kość:

• Brak pasującego indeksu unikalnego, brak UPSERT. ON CONFLICT(col) wymaga, żeby col była PRIMARY KEY albo miała ograniczenie UNIQUE. W przeciwnym razie SQLite zgłasza błąd „no such constraint”.
• DO UPDATE nie działa, jeśli nie ma konfliktu. To alternatywa dla wstawienia, a nie dodatkowe zachowanie. Gdy klucz pojawia się pierwszy raz, wykonuje się tylko wstawienie.
• excluded jest tylko do odczytu. Możesz z niej czytać, ale nie możesz do niej pisać. Celem SET jest zawsze istniejący wiersz.
• Generowane rowid dla INTEGER PRIMARY KEY. Jeśli nie podasz id, każde wstawienie dostaje nowe, więc nie ma z czym kolidować. UPSERT ma sens tylko wtedy, gdy kolidująca kolumna ma deterministyczną wartość podaną przez wywołującego.

Dalej: RETURNING

UPSERT nie mówi, które wiersze zostały wstawione, a które zaktualizowane, ani jak wyglądają ich końcowe wartości. Do tego służy klauzula RETURNING: zwraca zmienione wiersze w tej samej instrukcji, bez dodatkowego SELECT. O tym dalej.