Naprawianie struktury tabeli, aby uniknąć „Błąd: zduplikowana wartość klucza narusza unikalne ograniczenie”

Mam tabelę, która jest tworzona w ten sposób:

--
-- Table: #__content
--
CREATE TABLE "jos_content" (
  "id" serial NOT NULL,
  "asset_id" bigint DEFAULT 0 NOT NULL,
   ...
  "xreference" varchar(50) DEFAULT '' NOT NULL,
  PRIMARY KEY ("id")
);

Później wstawiane są niektóre wiersze określające identyfikator:

INSERT INTO "jos_content" VALUES (1,36,'About',...)

W późniejszym momencie niektóre rekordy są wstawiane bez identyfikatora i one niepowodzeniem z błędem: Error: duplicate key value violates unique constraint.

Najwyraźniej identyfikator został zdefiniowany jako sekwencja:

Każde nieudane wstawienie zwiększa wskaźnik w sekwencji, aż zwiększy się do wartości, która już nie istnieje, a zapytania zakończą się powodzeniem.

SELECT nextval('jos_content_id_seq'::regclass)

Co jest złego w definicji tabeli? Jaki jest sprytny sposób to naprawić?

W definicji tabeli nie ma nic złego.
_{(Z wyjątkiem kapelusz użyłbym jos_content_idlub coś zamiast non-opisowej nazwy kolumny id.
I prawdopodobnie używać textzamiastvarchar(50) .}

Twoje INSERToświadczenie stanowi problem.

Po idzdefiniowaniu kolumny jako serialnie należy wstawiać wartości ręcznych dla id. Mogą one kolidować z następną wartością z powiązanej sekwencji.

Podaj wyraźną listę kolumn docelowych (co prawie zawsze jest dobrym pomysłem dla utrwalonych INSERTinstrukcji) i całkowicie pomiń kolumny szeregowe .

INSERT INTO jos_content(asset_id, some_column, ...)
VALUES (36,'About',...);

Jeśli potrzebujesz natychmiastowych wartości automatycznie generowanych kolumn, skorzystaj z RETURNINGklauzuli :

INSERT ...
RETURNING id;  -- possibly more

Więcej szczegółów w tej pokrewnej odpowiedzi na temat SO:

• Wartość przyrostu identyfikatora tabeli dla każdego WSTAWU

Jeśli masz ręczne wpisy w serialkolumnach, które mogą później spowodować konflikt, ustaw sekwencję na bieżące maksimum, idaby to naprawić raz :

SELECT setval('jos_content_id_seq', max(id))
FROM   jos_content;

Gdzie jos_content_id_seqjest domyślną nazwą sekwencji należącej do jos_content.id, którą już znalazłeś w kolumnie domyślnej. Wydaje się, że tak jest xhzt8_content_id_seqw twoim przypadku;

Aktualizacja: Podobny problem pojawił się na SO i wymyśliłem nowe rozwiązanie:

• Niech Postgres wybierze następny minimalny dostępny identyfikator