UNIQUE mit NULL

Hey, ich habe folgende Tabelle:

CREATE TABLE foo
(
  id serial NOT NULL,
  a integer NOT NULL,
  b integer NOT NULL,
  c text,
  CONSTRAINT foo_pkey PRIMARY KEY (id),
  CONSTRAINT foo_a_b_c_key UNIQUE (a, b, c)
);

Nun war mir aufgefallen, dass das UNIQUE nicht greift, wenn c NULL ist. Also folgendes ist möglich:

id    a     b     c

1     1     2     NULL
2     1     2     NULL

Ist das normal oder ein Fehler? Ich gehe davon aus, dass es normal ist, da man NULL in pg auch nicht mit dem Gleichheitsoperator erwischt, nur mit IS. Die Begründung dafür in der Dokumentation finde ich… fragwürdig, um es nett auszudrücken.

Ich werde ja vermutlich nicht die erste Person sein, die in dieser Situation ist. Ich verwende postgresql-server-9.2.4, die letzte stabile Version meiner Distribution. Gibt es eine Möglichkeit für Einzigartigkeit trotz NULL Wert zu sorgen?

Vielleicht sowas wie hier:

test=*# create table bugmenot (id serial primary key, a int not null, c text);
CREATE TABLE
Time: 35,965 ms
test=*# create unique index idx_notnull on bugmenot (coalesce(c, 'nicht erlaubt hier'));
CREATE INDEX
Time: 2,892 ms
test=*# insert into bugmenot values (1, 1, null);
INSERT 0 1
Time: 0,785 ms
test=*# insert into bugmenot values (2, 2, null);
ERROR:  duplicate key value violates unique constraint "idx_notnull"
DETAIL:  Key ((COALESCE(c, 'nicht erlaubt hier'::text)))=(nicht erlaubt hier) already exists.
Time: 0,379 ms

Anpassung an Deine Tabelle überlasse ich Dir zur Übung.

Sehr größzügig :p Nein, danke für die Hilfe, COALESCE zu nutzen ist auf jeden Fall schon mal eine gute Idee. Das geht aber leider nicht in allen Fällen. Ein leerer String (oder ein langer Text wie bei dir) ist nunmal was Anderes als NULL :frowning: Ich bin mir nicht sicher, ob eine Situation auftreten wird, in der zwischen NULL und ‘’ unterschieden werden muss (oder einem beliebigen String), aber es wäre nicht komplett unvorstellbar. Daher wäre die Lösung ehr unsauber, aber wenn es keinen anderen Weg gibt, werde ich es so machen.

create unique index idx_notnull on foo (a, b, coalesce(c, ''));

funktioniert einwandfrei, aber ist es auch möglich das coalesce direkt ins CONSTRAINT des CREATE TABLE zu schreiben? Einfach “c” durch “coalesce(c, ‘’)” zu ersetzen funktioniert nicht und in der Dokumentation finde ich leider nichts dazu.

Du wolltest ein UNIQUE (über die table), Constraints beziehen sich auf einzelne Rows. Ich denke, Du hast, was Du gesucht hast, oder?

Ich fürchte, ich verstehe den Unterschied nicht. Ich bin davon ausgegangen, dass

CREATE TABLE foo1
(
  a integer NOT NULL,
  b integer NOT NULL,
  CONSTRAINT foo1_a_b_key UNIQUE (a, b)
);

das Gleiche macht wie

CREATE TABLE foo2
(
  a integer NOT NULL,
  b integer NOT NULL
);

CREATE UNIQUE INDEX idx_foobar ON foo2 (a, b);



INSERT INTO foo1(a, b) VALUES (1, 2);   -- Ja
INSERT INTO foo1(a, b) VALUES (2, 1);   -- Ja
INSERT INTO foo1(a, b) VALUES (1, 3);   -- Ja
INSERT INTO foo1(a, b) VALUES (1, 2);   -- Nein

INSERT INTO foo2(a, b) VALUES (1, 2);   -- Ja
INSERT INTO foo2(a, b) VALUES (2, 1);   -- Ja
INSERT INTO foo2(a, b) VALUES (1, 3);   -- Ja
INSERT INTO foo2(a, b) VALUES (1, 2);   -- Nein

Also mein Verständnis war, dass das CONSTRAINT intern automatisch dafür sorgt, dass ein Index erstellt wird und nur eine kompaktere Schreibweise ist. Aber ich sehe grade, dass das nicht stimmt. Verstehen tue ich den Unterschied leider dennoch nicht. Gibt es irgendeine Daumenregel, wann was besser geeignet ist? Da ich mit großen Mengen an Daten arbeite, ist mir vor allem Performance sehr wichtig.

Ansonsten ist das COALESCE leider nur zu 99% was ich gesucht habe. Wie ich oben geschrieben habe, trifft es leider nicht auf alle möglichen Fälle zu (ich hatte es später noch mal editiert). Aber da ich jetzt weiß, dass ich über CREATE INDEX weitere Befehle benutzen kann, werde ich selbst noch mal rumprobieren.

Falls jemand das gleiche Problem haben sollte: Die Lösung besteht dadrin, zwei UNIQUE Indexe für die Tabelle anzulegen. Einen für den Fall, dass c NULL ist und einen für den Fall, dass es nicht NULL ist. Siehe hier: http://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns