使用 LIKE 对数组进行 Postgres 查询 [英] Postgres Query of an Array using LIKE
问题描述
我正在使用 psql 在 Postgres 中查询数据库.我使用以下查询来搜索名为 tags 的字段,该字段具有文本数组作为数据类型:
I am querying a database in Postgres using psql. I have used the following query to search a field called tags that has an array of text as it's data type:
select count(*) from planet_osm_ways where 'highway' = ANY(tags);
我现在需要创建一个查询,在 tags 字段中搜索以字母A"开头的任何单词.我尝试了以下方法:
I now need to create a query that searches the tags fields for any word starting with the letter 'A'. I tried the following:
select count(*) from planet_osm_ways where 'A%' LIKE ANY(tags);
这给了我一个语法错误.关于如何将 LIKE 用于文本数组的任何建议?
This gives me a syntax error. Any suggestions on how to use LIKE with an array of text?
推荐答案
使用unnest()
函数将数组转换为行集:
Use the unnest()
function to convert array to set of rows:
SELECT count(distinct id)
FROM (
SELECT id, unnest(tags) tag
FROM planet_osm_ways) x
WHERE tag LIKE 'A%'
count(dictinct id)
应该计算 planet_osm_ways
表中的唯一条目,只需将 id
替换为您的主键名称.
The count(dictinct id)
should count unique entries from planet_osm_ways
table, just replace id
with your primary key's name.
话虽如此,您真的应该考虑将标签存储在一个单独的表中,与 planet_osm_ways
建立多对一的关系,或者为具有多对 - 的标签创建一个单独的表 -与 planet_osm_ways
有很多关系.您现在存储标签的方式使得在搜索标签时无法使用索引,这意味着每次搜索都会执行一次全表扫描.
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.
这篇关于使用 LIKE 对数组进行 Postgres 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!