1

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

IMSoP
  • 89,526
  • 13
  • 117
  • 169
Craig
  • 161
  • 7
  • 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 Feb 18 '21 at 18:26
  • 1
    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 Feb 18 '21 at 18:27
  • Since PostgreSQL supports [triggers](https://www.postgresql.org/docs/current/sql-createtrigger.html) you can explore `BEFORE INSERT`. – PM 77-1 Feb 18 '21 at 18:28
  • 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 Feb 18 '21 at 22:11

1 Answers1

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

Sotis
  • 176
  • 1
  • 9