sql - 我们如何使用约束来确保数组的任何值都不为 NULL？

Question

假设我有下表:

CREATE TABLE test (
    arr_column VARCHAR[] NOT NULL
)

这不会阻止在插入行时将数组的值设置为 NULL。所以，我想要一个约束来执行这个规则。我的尝试如下:

CREATE TABLE test (
    arr_column VARCHAR[] NOT NULL 
        CHECK (NOT (ARRAY[NULL]::VARCHAR[] <@ arr_column))
)

但不幸的是，如果我插入，这不会失败:

INSERT INTO test (ARRAY['some_string', NULL]::VARCHAR[])

Answer 1

进行此类检查的一种简单直接的方法是使用触发器，但您也可以简单地创建一个函数并像您目前所做的那样在 CHECK 子句中使用它:

CREATE OR REPLACE FUNCTION check_null_element(arr TEXT[]) 
RETURNS BOOLEAN AS $BODY$
DECLARE j INT;
BEGIN
  FOR j IN 1 .. ARRAY_UPPER(arr, 1) LOOP
    IF arr[j] IS NULL THEN
       RETURN FALSE;
    END IF;
  END LOOP;
RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql;

因此，在创建表格时，您只需要:

CREATE temp TABLE test (
    arr_column VARCHAR[] NOT NULL 
        CHECK (check_null_element(arr_column))
);

尝试插入具有 NULL 值的数组:

db=# INSERT INTO test VALUES (ARRAY['some_string', NULL]::VARCHAR[]);
FEHLER:  neue Zeile für Relation »test« verletzt Check-Constraint »test_arr_column_check«
DETAIL:  Fehlgeschlagene Zeile enthält ({some_string,NULL}).

还有一个有效 ..

db=# INSERT INTO test VALUES (ARRAY['some_string', 'NOT NULL :-)']::VARCHAR[]);
INSERT 0 1

编辑:很高兴拥有:

为了避免不需要的异常，您可以另外检查参数本身是否为 NULL - 对于这个问题来说是多余的，因为已经用 NOT NULL 约束在CREATE TABLE 语句。这可以通过向函数添加以下条件来完成: IF arr IS NULL THEN RETURN FALSE;如果结束；

CREATE OR REPLACE FUNCTION check_null_element(arr TEXT[]) 
RETURNS BOOLEAN AS $BODY$
DECLARE j INT;
BEGIN
  IF arr IS NULL THEN RETURN FALSE; END IF;
  FOR j IN 1 .. ARRAY_UPPER(arr, 1) LOOP
    IF arr[j] IS NULL THEN
       RETURN FALSE;
    END IF;
  END LOOP;
RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql;