如何在 PostgreSQL hstore 中使用通配符查询值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12520375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query values with wildcards in PostgreSQL hstore
提问by Rob Gonzalez
I'm trying to query hstore for all the values of a certain key that match a search criteria.
我正在尝试在 hstore 中查询与搜索条件匹配的某个键的所有值。
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%'
or:
或者：
SELECT DISTINCT
svals(slice(data, ARRAY['Supplier'])) AS sup
FROM "products"
WHERE lower(sup)
LIKE '%tosh%'
for case-insensitive search. How is this done?
用于不区分大小写的搜索。这是怎么做的？
回答by willglynn
You can extract values by key from an hstorecolumn with the ->operator.
您可以hstore使用->运算符从列中按键提取值。
SELECT data->'Supplier' AS sup
FROM products
WHERE lower(data->'Supplier') LIKE '%tosh%';
Additionally, like most expressions in PostgreSQL (excepting things like random()), you can index this value:
此外，像 PostgreSQL 中的大多数表达式一样（除了像random()），你可以索引这个值：
CREATE INDEX products_supplier_key ON products ((data->'Supplier'));
CREATE INDEX products_supplier_lowercase_key ON products ((lower(data->'Supplier')));
This would allow PostgreSQL to answer many such queries using the index instead of fetching each row and scanning the hstorecolumn. See the notes on Index Typesregarding index usage with LIKE.
这将允许 PostgreSQL 使用索引来回答许多此类查询，而不是获取每一行并扫描hstore列。请参阅关于索引类型的注释，了解 LIKE 的索引用法。
回答by jredburn
One caveat to willglynn's answer for anyone seeing this in the future -- the original query and the new query have slightly different behavior. Namely,
对 willglynn 对将来看到这一点的任何人的回答的一个警告——原始查询和新查询的行为略有不同。即，
SELECT data->'Supplier' AS sup
FROM products;
will include a NULL value assuming at least one row doesn't have an assignment for Supplier.
将包含一个 NULL 值，假设至少有一行没有对供应商的分配。
SELECT DISTINCT
svals(slice(data, ARRAY['Supplier']))
FROM "products"
will not return the NULL value.
不会返回 NULL 值。
