uniqueness constraint with NULLs
От | Robert Edwards |
---|---|
Тема | uniqueness constraint with NULLs |
Дата | |
Msg-id | 4A485431.3080707@cs.anu.edu.au обсуждение исходный текст |
Ответы |
Re: uniqueness constraint with NULLs
Re: uniqueness constraint with NULLs |
Список | pgsql-sql |
I have a table with a uniqueness constraint on three columns: # \d bobtest Table "public.bobtest" Column | Type | Modifiers --------+---------+------------------------------------------------------ id | integer | not null default nextval('bobtest_id_seq'::regclass)a | integer | b | integer | c | integer | Indexes: "bobtest_id_key" UNIQUE, btree (id) "bobtest_unique" UNIQUE, btree (a, b, c) I can insert multiple rows with identical a and b when c is NULL: ... # insert into bobtest (a, b) values (1, 4); INSERT 0 1 # insert into bobtest (a, b, c) values (1, 4, NULL); INSERT 0 1 # select * from bobtest; id | a | b | c ----+---+---+--- 1 | 1 | 2 | 1 2 | 1 | 3 | 1 3 | 1 | 4 | 1 4 | 1 | 4 | 5 | 1 | 4 | 6 | 1 | 4 | (6 rows) Can anyone suggest a way that I can impose uniqueness on a and b when c is NULL? In the real app., c is a date field and I require it to be NULL for some rows. In these cases, I only want at most one row with identical a and b, but I can have identical a and b when c is a real date as long as that date is also unique for a given a and b. I'm guessing I'm going to need to use a function and that someone will yell at me for using NULLs to represent real data, but I thought I'd be brave and ask anyway, in case I am missing some other solution that doesn't involve the use of triggers etc. Cheers, Bob Edwards.
В списке pgsql-sql по дате отправления: