问题描述
我有一个名为posts的Postgresql 11.8表，我想在其中定义JSONB类型的列slugs，该列将包含字符串数组，例如["my-first-post", "another-slug-for-my-first-post"].
I have a Postgresql 11.8 table named posts where I would like to define a column slugs of type JSONB, which would contain arrays of strings such as ["my-first-post", "another-slug-for-my-first-post"].
我可以使用?存在运算符:SELECT * FROM posts WHERE slugs ? 'some-slug'找到具有特定子句的帖子.
I can find a post having a specific slug using the ? existence operator: SELECT * FROM posts WHERE slugs ? 'some-slug'.
每个帖子预计只有少数几个，但是帖子的数量有望增长.
Each post is expected to only have a handful of slugs but the amount of posts is expected to grow.
考虑以上查询，其中some-slug可以是任何字符串:
Considering the above query where some-slug could be any string:
- 如何定义索引以进行合理的查询(不进行全表扫描)?
- 如何确保同一条弹头不能多次出现(在不同阵列中以及在不同阵列中)?
我主要是在寻找Postgresql 11的解决方案，但也希望了解将来版本中的解决方案.
I am primarily looking for a solution for Postgresql 11 but also would be interested to know solutions in future versions, if any.
该数据库在Rails 6.0应用程序中使用，因此我对Rails迁移语法也很感兴趣.
The database is used in a Rails 6.0 app so I am also interested by the Rails migration syntax.
推荐答案
您可以在jsonb列上使用常规GIN索引来支持?运算符.
You can support the ? operator with a normal GIN index on the jsonb column.
但是，您不能强制使用数组值的唯一性.
However, you cannot enforce uniqueness of array values.
尤其是如果您希望具有数据库约束，则应该不使用JSON为数据建模.使用带有多个表和外键的常规规范化数据模型，可以很容易地实现这种唯一性约束.
Particularly if you want to have database constraints, you should not model your data using JSON. Use a regular normalized data model with several tables and foreign keys, then it will be easy to implement such an uniqueness constraint.
这篇关于为元素的存在和唯一性索引Postgresql JSONB数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！