One row per rare type
If you want to allow 0-n rows with type 'c' and only 0-1 rows for type 'a' and 'b' (each), you can combine a simple CHECK
constraint with a partial unique index:
CREATE TABLE example (
example_id serial PRIMARY KEY,
example_state "char" NOT NULL CHECK (example_state IN ('a', 'b', 'c'))
);
CREATE UNIQUE INDEX example_unique_exception_idx ON example (example_state)
WHERE example_state <> 'c'; -- column is not null;
Related answer:
An exlusion constraint would be a related concept, but it won't work with multiple entries for 'c'
. You could use NULL
instead of 'c' to make that work.
Also note the special data type "char"
(in double-quotes!), which seems to be perfect for your simple purpose (unless it's just a simplification for the purpose of the question). It only occupies a single byte - as opposed to char(1)
, which needs at least 2 bytes on disk and 5 in RAM.
Singleton row with any rare type
To restrict to a single row overall that does not have type 'c', use a partial unique index on an expression:
CREATE UNIQUE INDEX example_single_exception_idx ON example ((example_state <> 'c'))
WHERE example_state <> 'c';
('a', 'b')
- or 0-1 rows with 'b' and 0-1 rows with 'a'? – Erwin Brandstetter Aug 4 '14 at 17:15