Postgres Query of an Array using LIKE
Here is another way to do it within the WHERE
clause:
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%'
)
);
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%'
The count(dictinct id)
should count unique entries from planet_osm_ways
table, just replace id
with your primary key's name.
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.