How to impose this exclusion constraint?

Question

I have a key-value table.

CREATE TABLE keyvalues (
  key TEXT NOT NULL,
  value TEXT
)

I want to impose a constraint that if a key has an entry with NULL value, it cannot have any other entries. How do I do that?

To clarify: I want to allow ("key1", "value1"), ("key1", "value2"). But if I have ("key2", NULL), I want to not allow ("key2", "value3").

Answer 1

You can use a trigger, like this:

CREATE OR REPLACE FUNCTION trigger_function()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
 if exists (select 1 from keyvalues key = new.key and value is null) then 
     RAISE EXCEPTION 'Key-value not allowed';
  end if;
  RETURN new;
end;
$function$
;

Then you create the trigger on the table

CREATE TRIGGER trigger_on_table
BEFORE INSERT OR UPDATE
    ON keyvalues
    FOR EACH ROW
        EXECUTE PROCEDURE trigger_function();

And test it:

insert INTO keyvalues 
SELECT 'a','a'

OK

insert INTO keyvalues 
SELECT 'a','b'

OK

insert INTO keyvalues 
SELECT 'b',null

OK

insert INTO keyvalues 
SELECT 'b','b'

ERROR: Key-value not allowed