使用数组的Postgres的查询LIKE [英] Postgres Query of an Array using LIKE

问题描述

我在使用Postgres的PSQL查询数据库。我用下面的查询来搜索一个名为场的标签的有文字的数组，因为它的数据类型：

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);

我现在需要创建一个查询，搜索的标签的字段开头字母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%'

的计数（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.

这篇关于使用数组的Postgres的查询LIKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！