如何在 PostgreSQL hstore 中使用通配符查询值 [英] How to query values with wildcards in PostgreSQL hstore

问题描述

我正在尝试在 hstore 中查询与搜索条件匹配的某个键的所有值.

I'm trying to query hstore for all the values of a certain key that match a search criteria.

我可以像这样获取某个键的所有值:

I can get all the values for a certain key like this:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier']))
FROM "products"

我也可以得到一个特定的值:

I can also get a specific value:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE data @> 'Supplier => Toshiba'

我真正想要的是(这不起作用):

What I would really like is something like (this doesn't work):

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE data @> 'Supplier => %tosh%'

或:

SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE lower(sup)
LIKE '%tosh%'

用于不区分大小写的搜索.这是怎么做的?

for case-insensitive search. How is this done?

推荐答案

您可以使用 -> 运算符.

You can extract values by key from an hstore column with the -> operator.

SELECT data->'Supplier' AS sup
FROM products
WHERE lower(data->'Supplier') LIKE '%tosh%';

另外，像 PostgreSQL 中的大多数表达式一样(除了 random() 之类的东西)，你可以索引这个值:

Additionally, like most expressions in PostgreSQL (excepting things like random()), you can index this value:

CREATE INDEX products_supplier_key ON products ((data->'Supplier'));
CREATE INDEX products_supplier_lowercase_key ON products ((lower(data->'Supplier')));

这将允许 PostgreSQL 使用索引来回答许多此类查询，而不是获取每一行并扫描 hstore 列.请参阅索引类型的注释，了解 LIKE 的索引用法.

This would allow PostgreSQL to answer many such queries using the index instead of fetching each row and scanning the hstore column. See the notes on Index Types regarding index usage with LIKE.

这篇关于如何在 PostgreSQL hstore 中使用通配符查询值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！