时间戳

首页 > 解决方案 > 如何为 postgresql 中的唯一（不包括顺序）JSONB 列创建约束

python - 如何为 postgresql 中的唯一（不包括顺序）JSONB 列创建约束

问题描述

我正在寻找一种创建约束的方法，该约束将验证 JSONB 列中的值是唯一的，但是我需要它们在不考虑顺序的情况下进行比较时也是唯一的，因此 JSONB 比较/散列对于这种情况无效。

为了检查 2 个 JSONB 对象是否相等，我检查它们是否相互包含

select t.a @> t.b and  t.a <@ t.b
from (
SELECT
    '{"foo": 42, "bar": ["value", 5]}'::jsonb as a,
    '{"bar": [5, "value"], "foo": 42}'::jsonb as b
) as t

具体来说，我正在寻找一个 SQLAlchemy 解决方案，但一个原始的 postgresql 解决方案可能会有很大帮助。

标签： pythonjsonpostgresqlsqlalchemyjsonb

解决方案

我的解决方案是添加一个触发器，该触发器调用一个函数，该函数检查是否已经存在一个 jsonb，该 jsonb 包含并被正在插入的那个

扳机：

create trigger trigger1
before insert
on json_table
for each row
execute procedure func;

功能：

create function func returns trigger
language plpgsql
as
$$
declare
same_json json_table%rowtype;
BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')  THEN
        select * from json_table
        into same_json
        where NEW.json @> json_table.json and NEW.json <@ json_table.json;

        IF FOUND then
            RAISE NOTICE 'Not inserting json, an equal json is already in the table: id=%', same_json.id;
            return NULL;
        END IF;
    END IF;
    RETURN NEW;
END;
$$;

然后我创建了一个手动 alembic 迁移，它添加了触发器和函数 - 请参阅https://github.com/sqlalchemy/alembic/issues/504#issuecomment-441887901

推荐阅读