Supabase "duplicate key value violates unique constraint" erklärt

Jemand füllt das Formular für deine Warteliste aus, wartet und drückt den Knopf noch einmal. Der erste Klick war schon durchgegangen. Der zweite kommt als Fehler zurück, und die Browserkonsole zeigt alles:

{
  "code": "23505",
  "details": "Key (email)=(maria@example.com) already exists.",
  "hint": null,
  "message": "duplicate key value violates unique constraint \"waitlist_email_key\""
}

Supabase "duplicate key value violates unique constraint" heißt, dass Postgres die Zeile abgelehnt hat, weil eine andere Zeile denselben Wert schon in einer Spalte hat, die jeden Wert nur einmal erlaubt. Hier ist die Spalte email, und Maria steht schon auf deiner Liste.

Der Rat, der für diesen Fehler herumgereicht wird, lautet: das Insert auf ein Upsert umstellen. Manchmal stimmt das. Auf einer Tabelle, in die Besucher nur eintragen dürfen, braucht ein Upsert aus dem Browser allerdings Regeln, die ihnen das Lesen der Tabelle erlauben, und die Zeile mit Marias Adresse sollte deine App einem Besucher überhaupt nie zeigen.

Was bedeutet "duplicate key value violates unique constraint"?

Der Wert, den du speichern wolltest, steht schon in einer Spalte, die jeden Wert nur einmal nimmt, also hat Postgres die ganze Zeile abgelehnt. Geschrieben wurde nichts, und die Zeile, die zuerst da war, ist unverändert.

Stell dir die Tabelle als Gästeliste an einer Tür vor, eine Zeile pro Name. Eine Unique-Regel ist die Regel des Türstehers, dass ein Name nur einmal auf die Liste kommt. Deine App kam mit einem Namen, der schon dasteht, und er hat sie abgewiesen.

Jeder Teil des Fehlers sagt eine Sache:

Der Wortlaut stammt von Postgres selbst. Der Code, der den Fehler auslöst, hängt die Zeile Key ... already exists nur an, wenn er eine Beschreibung des Schlüssels hat, und ein Abschnitt weiter unten zeigt, wann er keine hat.

Welche Spalte hat den Fehler ausgelöst?

Das sagt der Name in Anführungszeichen, denn Postgres baut ihn aus Tabelle und Spalten, solange niemand einen anderen gewählt hat. Sein Code für die Namen kennt drei Formen:

Die Meldung sagt bei allen dreien "unique constraint". Um genau zu sehen, was ein Name abdeckt, führ das im SQL Editor aus, mit dem Namen aus deiner Meldung:

select tablename, indexdef
from pg_indexes
where indexname = 'waitlist_email_key';

indexdef kommt etwa als CREATE UNIQUE INDEX waitlist_email_key ON public.waitlist USING btree (email) zurück, und die Spalten in den Klammern sind die, die sich nicht wiederholen dürfen. Eine Regel auf zwei Spalten, etwa (team_id, email), lässt dieselbe Adresse in zwei Teams zu und lehnt sie im selben Team beim zweiten Mal ab.

"Key (id)=(1) already exists", obwohl du nie eine id geschickt hast

Hier hinkt der ID-Zähler der Tabelle den Zeilen hinterher, die schon drin sind, also hat er eine Nummer vergeben, die besetzt ist. Deine App hat keine id geschickt. Postgres hat die nächste Nummer vom Zähler genommen, und eine Zeile mit dieser Nummer gab es schon.

Auf der Gästeliste verteilt der Türsteher auch nummerierte Armbänder von einer Rolle. Jemand hat die ersten zweihundert Gäste von Hand eingetragen, samt Armbandnummern, und niemand hat die Rolle weitergedreht. Sie fängt immer noch bei 1 an.

Eine Spalte, die sich selbst nummeriert, nimmt jede Nummer aus einer Sequenz, so nennt Postgres diesen Zähler. Postgres' Seite über Identity-Spalten sagt, eine solche Spalte "does not guarantee uniqueness", und nennt genau diesen Fall als einen der Gründe: Werte "could be inserted manually into the identity column". Zeilen kommen mit schon ausgefüllten Nummern an, aus einem CSV-Import mit der Spalte id, einer Kopie aus einem anderen Projekt oder einem Seed-Skript. Der Zähler erfährt von keiner davon. Supabases Troubleshooting-Seite über ID-Sequenzen behandelt genau diese Meldung bei einem _pkey, und ihre Lösung ist ebenfalls ein setval.

Die Reparatur ist eine Anweisung. Setz deine eigene Tabelle und Spalte statt orders und id ein; die Tabelle ist der Teil des Namens vor _pkey:

select setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (select max(id) from public.orders)
);

pg_get_serial_sequence findet den Zähler hinter der Spalte, und die Dokumentation von Postgres sagt, dass das für beide Arten funktioniert: "If the column is an identity column, the associated sequence is the sequence internally created for that column." Danach stellt setval mit zwei Argumenten den Zähler so, dass "the next nextval will advance the sequence before returning a value". Die nächste neue Zeile bekommt die höchste id plus eins. Antwortet Postgres, dass die Relation nicht existiert, ist die Tabelle anders geschrieben, hat einen Großbuchstaben oder liegt in einem anderen Schema, und dieser Fehler hat einen eigenen Beitrag.

Eine Wiederherstellung aus der Ausgabe von pg_dump bringt dich nicht hierher. Seine Dokumentation zählt auf, was ein Datendump enthält: "Table data, large objects, and sequence values are dumped." Jeder Zähler kommt mit seinen Zeilen zurück.

Die drei Wege, mit einem Duplikat umzugehen

Entscheide, was passieren soll, wenn der Wert schon da ist: die neue Zeile ablehnen und das sagen, sie die alte ersetzen lassen oder sie gar nicht erst schicken.

Lehn sie ab, mit eigenen Worten. Das passt, wenn eine zweite Zeile ein Fehler wäre: eine Adresse, die schon registriert ist, ein Nutzername, der schon vergeben ist. Das Insert scheitert, deine App liest den Code, und die Person sieht einen Satz, den du geschrieben hast:

const { error } = await supabase.from('profiles').insert({ id: user.id, username })

if (error?.code === '23505') {
  setMessage('Dieser Nutzername ist vergeben. Versuch einen anderen.')
} else if (error) {
  setMessage('Etwas ist schiefgegangen. Bitte versuch es noch einmal.')
}

Lass sie die alte ersetzen. Das passt, wenn die neueste Fassung gewinnen soll: eine Einstellungszeile pro Nutzer, ein Warenkorb pro Sitzung. Das ist ein Upsert, und der nächste Abschnitt zeigt, wann er schiefgeht.

Schick sie nicht. Manchmal sollte es die zweite Zeile nie geben, und sie kommt aus einer dieser Quellen:

• ein Knopf, der das Formular noch einmal schickt, wenn man ihn noch einmal drückt. Deaktivier ihn, bis die erste Anfrage antwortet.
• ein neuer Versuch nach einer langsamen Antwort, obwohl der erste durchgegangen war
• zwei Schreiber für eine Zeile, etwa ein Trigger, der für jeden neuen Nutzer ein Profil anlegt, und App-Code, der dasselbe Profil nach der Registrierung einfügt

Den Trigger-Fall solltest du auf Anhieb erkennen. Ein Trigger, der neue Nutzer in eine eigene Tabelle kopiert, scheitert mit diesem Fehler, wenn der kopierte Wert dort schon steht, und Supabase meldet die ganze Registrierung als "Database error saving new user". Den 23505 selbst findest du in den Postgres-Logs, und dieser Beitrag zeigt, wo.

Solltest du upsert benutzen?

Benutz es, wenn die neue Zeile die alte ersetzen soll, und prüf drei Dinge, bevor du dich darauf verlässt.

.upsert() in supabase-js ist ein Insert, das etwas anderes tut, wenn die Zeile existiert. Sein Quellcode sagt es in zwei Zeilen: "If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows." Das ist die Option ignoreDuplicates, und Postgres führt beide Fassungen als insert ... on conflict aus.

1. onConflict muss die Spalte aus deinem Fehler nennen.Die Dokumentation von PostgREST sagt: "By default, upsert operates based on the primary key columns". Ein Upsert von { email } in eine Tabelle mit dem Primärschlüssel id sucht nach einer Kollision bei id, findet keine, weil die id neu ist, fügt ein und trifft auf die Regel für email, mit demselben 23505. Die INSERT-Referenz von Postgres sagt, die Ausweichaktion greift, "if an arbiter constraint or index specified by conflict_target is violated". Eine Kollision in jeder anderen Spalte lehnt die Zeile ab wie vorher.

await supabase
  .from('settings')
  .upsert({ user_id: user.id, theme: 'dark' }, { onConflict: 'user_id' })

2. Diese Spalte braucht eine eigene Unique-Regel. Ohne sie hat Postgres nichts zum Abgleichen und antwortet there is no unique or exclusion constraint matching the ON CONFLICT specification, Code 42P10 (die Zeile, die das auslöst).

3. Aus dem Browser liest Row Level Security mehr von deinen Regeln. Row Level Security sind die Regeln pro Zeile, die Supabase bei jeder Anfrage mit deinem öffentlichen Schlüssel prüft. Für einen Upsert sagt Postgres' Referenz zu Policies: "SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation's SELECT policies." Existiert die Zeile und der Upsert führt zusammen, wird die vorhandene Zeile zusätzlich gegen die Update-Regeln der Tabelle geprüft. Eine Zeile, die eine der beiden Prüfungen nicht besteht, beendet die Anfrage mit new row violates row-level security policy.

Auf der Warteliste scheitert ein Upsert aus dem Browser also, bis die Tabelle eine Leseregel hat, die ein Besucher besteht. Eine Leseregel, die ein Fremder für jede beliebige Adresse besteht, ist eine Regel, die Fremde deine Liste lesen lässt. An der Tür heißt eine Zeile überschreiben: Man bekommt die Liste vorher gezeigt. Bei so einer Tabelle lehn das Duplikat lieber ab, oder nimm die Funktion aus dem nächsten Abschnitt.

Ist es sicher, diesen Fehler deinen Nutzern zu zeigen?

Nein. Zeig einen eigenen Satz und behalte den rohen Fehler in deinen Logs, aus zwei Gründen: Die Detailzeile kann den Wert zurückliefern, und der Fehler bestätigt, dass der Wert existiert, egal was du anzeigst.

Die Detailzeile wiederholt den Wert. Key (email)=(maria@example.com) ist auf Marias eigenem Bildschirm harmlos und etwas ganz anderes in einem Screenshot in einem Supportforum, in einem Error-Tracker oder auf dem Bildschirm von jemandem, der die Adresse einer anderen Person in dein Formular getippt hat. Postgres schützt sie schon. Die Funktion, die die Zeile baut (BuildIndexValueDescription), prüft zuerst: "if RLS is enabled for the relation. If so, return NULL to avoid leaking data." Eine Anfrage mit deinem öffentlichen Schlüssel an eine Tabelle mit eingeschalteter Row Level Security bekommt "details": null.

Andersherum gelesen verrät dir die Zeile etwas über die Tabelle. Zeigt deine Browserkonsole Key (...)=(...) für eine Anfrage, die deine App mit dem öffentlichen Schlüssel gestellt hat, ist Row Level Security auf dieser Tabelle aus. Die eine Ausnahme ist eine Anfrage, die deine App über Servercode mit dem geheimen Schlüssel schickt, der die Regeln komplett überspringt. null beweist weniger: Postgres lässt die Zeile auch weg, wenn der Aufrufer nicht jede Spalte im Schlüssel lesen darf. Row Level Security für jede Tabelle einschalten zeigt den Schalter und die erste Regel.

Eine Tabelle mit ausgeschalteten Regeln antwortet einem Fremden genauso bereitwillig wie dir. Unser kostenloser Scan liest deine Live-App mit demselben öffentlichen Schlüssel, den ein Besucher bekommt, meldet, welche Tabellen Zeilen herausgeben, und braucht kein Konto.

Der Fehler beantwortet eine Frage, die du vielleicht nicht beantwortet haben willst. Auch ohne Detailzeile sagt ein 409 dem Absender, dass der Wert schon da ist. Postgres' Seite über Row Security sagt, dass Unique-Regeln "always bypass row security to ensure that data integrity is maintained", und bittet dich, Tabellen und Regeln so zu entwerfen, dass diese Prüfungen nicht zu einem verdeckten Kanal werden. Auf der Warteliste kann jeder herausfinden, ob eine Adresse auf deiner Liste steht, indem er sie einzutragen versucht. Der Türsteher liest den Namen nie vor, und wenn er jemanden abweist, weiß die Schlange trotzdem, dass er dastand.

Wenn das für deine Liste zählt, gib dem Browser in beiden Fällen dieselbe Antwort. Eine Funktion in der Datenbank kann die Adresse eintragen, sie überspringen, wenn sie schon da ist, und in beiden Fällen nichts zurückgeben:

create function public.join_waitlist(new_email text)
returns void
language sql
security definer
set search_path = ''
as $$
  insert into public.waitlist (email)
  values (new_email)
  on conflict (email) do nothing;
$$;

Deine App ruft supabase.rpc('join_waitlist', { new_email: email }) auf. security definer führt das Insert als Eigentümer der Funktion aus, also brauchen Besucher überhaupt keine Regel auf der Tabelle, und die Regel, die sie eintragen ließ, kann weg, sonst bleibt der alte Weg neben dem neuen offen. Was "Database error saving new user" über security definer sagt erklärt, was es abgibt, und eine Supabase-Funktion aufrufen zeigt, wie die Argumentnamen passen müssen.

Bevor du änderst, wie deine App schreibt

Behalte eine Kopie der Datenbank von vor dem Moment, in dem du ein Insert auf ein Upsert umstellst oder einen Zähler zurücksetzt, und erfahre es, wenn eine Tabelle anfängt, Fremden zu antworten.

Reeve Care bewahrt eine Kopie deiner Supabase-Datenbank auf:

• eine verschlüsselte Kopie nach Zeitplan, auf Reeves eigenem Speicher in der EU, außerhalb deines Supabase-Kontos
• jede Tabelle mit ihren Zeilen, wie sie waren, bevor ein Zusammenführen sie überschrieben hat
• der ID-Zähler jeder Tabelle kommt bei einer Wiederherstellung mit ihren Zeilen zurück, sodass die nächste neue Zeile eine freie Nummer bekommt
• jede Kopie wird geprüft, bevor sie zählt, indem die Zeilen in jeder Tabelle gezählt werden
• auch deine hochgeladenen Dateien, sobald du eine Storage-Zugangsberechtigung verbindest
• Wiederherstellung mit einem Klick, vorher wird eine Sicherheitskopie des aktuellen Stands angelegt
• alles, was Monitor macht

Reeve Monitor prüft weiter von außen:

• ob deine App erreichbar ist, jede Minute geprüft
• der vollständige Sicherheitscheck jede Stunde, für bis zu drei Apps
• eine Nachricht, wenn sich ein Ergebnis ändert, sodass eine Tabelle, die nach einem Deploy Fremden antwortet, dich erreicht, ohne dass du nachsehen musst
• ein monatlicher Bericht in klarer Sprache über das, was er gesehen hat

Was du jetzt tun solltest

Fang beim Namen in Anführungszeichen an, denn er sagt, welche Regel die Zeile abgelehnt hat und welche Spalte sie bewacht. Bevor du ein setval, ein Upsert oder irgendetwas anderes ausführst, das Zeilen umschreibt, auf die sich Leute verlassen, behalte eine Kopie der Datenbank von vor der Änderung.