PostgreSQL为jsonb列添加约束：确保包含指定字符串之一

场景1：列值为jsonb数组 #

如果你的columnTest存储的是jsonb数组（例如["test1", "bar"]这类格式），要确保数组中至少包含test1/test2/test3/test4中的一个，可以使用PostgreSQL内置的?|操作符实现约束：

ALTER TABLE tableTest
ADD CONSTRAINT tableTest_columnTest_check
CHECK (columnTest ?| ARRAY['test1', 'test2', 'test3', 'test4']::text[]);

?|操作符的作用是判断左侧的jsonb数组是否包含右侧文本数组中的任意一个元素，返回布尔值，完全匹配“至少包含一个预定义字符串”的需求。

场景2：列值为jsonb对象 #

如果columnTest存储的是jsonb对象（例如{"action": "test2"}这类格式），分两种情况处理：

• 检查对象的值包含预定义字符串：

ALTER TABLE tableTest
ADD CONSTRAINT tableTest_columnTest_check
CHECK (
  EXISTS (
    SELECT 1
    FROM jsonb_each_text(columnTest)
    WHERE value IN ('test1', 'test2', 'test3', 'test4')
  )
);

• 检查对象的键包含预定义字符串：
  把上述语句中的value替换为key即可。

你之前尝试的问题解析 #

• 第一个注释的语法是要求列值完全等于指定数组，不是“包含其中一个”，逻辑不符。
• 第二个columnTest <@ ARRAY[...]：<@操作符要求左侧jsonb的所有元素都在右侧列表中（即被完全包含），且类型不匹配（右侧是text数组，左侧是jsonb），无法生效。
• 第三个columnTest::jsonb ? 'attribute'：?只能检查单个元素/键，无法批量验证多个预定义值。
• 第四个CHECK (columnTest IN {...})：语法错误，IN不能直接跟json对象，逻辑也不符合需求。

内容的提问来源于stack exchange，提问作者Catto