postgresql 使用 LIKE 对数组进行 Postgres 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7222106/
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
Postgres Query of an Array using LIKE
提问by nix
I am querying a database in Postgres using psql. I have used the following query to search a field called tagsthat has an array of text as it's data type:
我正在使用 psql 在 Postgres 中查询数据库。我使用以下查询来搜索名为tags的字段，该字段的数据类型为文本数组：
select count(*) from planet_osm_ways where 'highway' = ANY(tags);
I now need to create a query that searches the tagsfields for any word starting with the letter 'A'. I tried the following:
我现在需要创建一个查询，在标签字段中搜索以字母“A”开头的任何单词。我尝试了以下方法：
select count(*) from planet_osm_ways where 'A%' LIKE ANY(tags);
This gives me a syntax error. Any suggestions on how to use LIKE with an array of text?
这给了我一个语法错误。关于如何将 LIKE 与文本数组一起使用的任何建议？
回答by Crack
Use the unnest()function to convert array to set of rows:
使用unnest()函数将数组转换为行集：
SELECT count(distinct id)
FROM (
SELECT id, unnest(tags) tag
FROM planet_osm_ways) x
WHERE tag LIKE 'A%'
The count(dictinct id)should count unique entries from planet_osm_waystable, just replace idwith your primary key's name.
本count(dictinct id)应算唯一条目planet_osm_ways表，只需更换id你的主键的名称。
That being said, you should really think about storing tags in a separate table, with many-to-one relationship with planet_osm_ways, or create a separate table for tags that will have many-to-many relationship with planet_osm_ways. The way you store tags now makes it impossible to use indexes while searching for tags, which means that each search performs a full table scan.
话虽如此，您真的应该考虑将标签存储在一个单独的表中，与 具有多对一关系planet_osm_ways，或者为与 具有多对多关系的标签创建一个单独的表planet_osm_ways。现在存储标签的方式使得在搜索标签时无法使用索引，这意味着每次搜索都执行全表扫描。
回答by Rotareti
Here is another way to do it within the WHEREclause:
这是在WHERE子句中执行此操作的另一种方法：
SELECT COUNT(*)
FROM planet_osm_ways
WHERE (
0 < (
SELECT COUNT(*)
FROM unnest(planet_osm_ways) AS planet_osm_way
WHERE planet_osm_way LIKE 'A%'
)
);
