Restrict two specific column values from existing at the same time

Question

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.

I would appreciate any help creating a constraint that will enforce this.

CREATE TABLE example
(
  example_id    serial PRIMARY KEY,
  example_state CHAR(1) NOT NULL
);

ALTER TABLE example ADD CONSTRAINT 
  example_constraint
CHECK (example_state = 'a' OR example_state = 'b' OR example_state = 'c');

Answer 1

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:

• PostgreSQL multi-column unique constraint and NULL values

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.

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

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';