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").

Huh? key is NOT NULL it can't have a NULL value. You are going to show some (pseudo)code explaining what you want to achieve. — Adrian Klaver
I mean an entry that looks like ("key", NULL). That is, the value field is NULL. I want to allow ("key1", "value1"), ("key1", "value2"). But if I have ("key2", NULL), I want to not allow ("key2", "value3"). — Craig
Since PostgreSQL supports triggers you can explore BEFORE INSERT. — PM 77-1
What if you have a value present for a key and try to insert a row with the same key and a NULL value? — clamp

Sotis Sotis · Accepted Answer · 2021-02-18T21:53:07

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

How to impose this exclusion constraint?

1 Answers