Postgres Query of an Array using LIKE
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.
Postgresql select where array overlaps using like
LIKE
operates on strings. To check whether two arrays overlap, you can use &&
.
From the Array Functions and Operators documentation:
&&
: overlap (have elements in common)
SELECT ARRAY[1,4,3] && ARRAY[2,1] arrays_overlap;
| arrays_overlap |
| -------------- |
| true |
To see if there are values in an array that are LIKE
those from another array, one solution would be to unnest both arrays and compare the results with LIKE
:
SELECT EXISTS (
SELECT 1
FROM
unnest(ARRAY['abc', 'def' ]) my_array(x)
INNER JOIN unnest (ARRAY['a', 'z' ]) my_keywords(x)
ON my_array.x LIKE '%' || my_keywords.x || '%'
) arrays_have_similar_elements;
| arrays_have_similar_elements |
| ---------------------------- |
| true |
SELECT EXISTS (
SELECT 1
FROM
unnest(ARRAY['abc', 'def' ]) my_array(x)
INNER JOIN unnest (ARRAY['y', 'z' ]) my_keywords(x)
ON my_array.x LIKE '%' || my_keywords.x || '%'
) arrays_have_similar_elements;
| arrays_have_similar_elements |
| ---------------------------- |
| false |
Demo on DB Fiddle
Postgres: check if array field contains value?
This should work:
select * from mytable where 'Journal'=ANY(pub_types);
i.e. the syntax is <value> = ANY ( <array> )
. Also notice that string literals in postresql are written with single quotes.
Find rows where text array contains value similar to input
It's also important to understand that ANY
is not an operator but an SQL construct that can only be used to the right of an operator. More:
- How to use ANY instead of IN in a WHERE clause with Rails?
The LIKE
operator - or more precisely: expression, that is rewritten with to the ~~
operator in Postgres internally - expects the value to the left and the pattern to the right. There is no COMMUTATOR
for this operator (like there is for the simple equality operator =
) so Postgres cannot flip operands around.
Your attempt:
select * from someTable where '%someInput%' LIKE ANY(someColum);
has flipped left and right operand so '%someInput%'
is the value and elements of the array column someColum
are taken to be patterns (which is not what you want).
It would have to be ANY(someColum) LIKE '%someInput%'
- except that's not possible with the ANY
construct which is only allowed to the right of an operator. You are hitting a road block here.
Related:
- Is there a way to usefully index a text column containing regex patterns?
- Can PostgreSQL index array columns?
You can normalize your relational design and save elements of the array in separate rows in a separate table. Barring that, unnest()
is the solution, as you already found yourself. But while you are only interested in the existence of at least one matching element, an EXISTS
subquery will be most efficient while avoiding duplicates in the result - Postgres can stop the search as soon as the first match is found:
SELECT *
FROM tbl
WHERE EXISTS (
SELECT -- can be empty
FROM unnest(someColum) elem
WHERE elem LIKE '%someInput%'
);
You may want to escape special character in someInput
. See:
- Escape function for regular expression or LIKE patterns
Careful with the negation (NOT LIKE ALL (...)
) when NULL
can be involved:
- Check if NULL exists in Postgres array
PostgreSQL wildcard LIKE for any of a list of words
You can use Postgres' SIMILAR TO
operator which supports alternations, i.e.
select * from table where lower(value) similar to '%(foo|bar|baz)%';
PostgreSQL: Where clause using LIKE, ANY, and wildcards
You have to unnest the array field:
with my_table(my_field) as (
values
(array['query medium', 'large query']),
(array['large query', 'small query'])
)
select t.*
from my_table t,
lateral unnest(my_field) elem
where elem ilike 'query%';
my_field
--------------------------------
{"query medium","large query"}
(1 row)
Related Topics
Cast VS Ssis Data Flow Implicit Conversion Difference
SQL Server - Dirty Reads Pros & Cons
Concat Group by in Vertica SQL
MySQL Duplicates with Load Data Infile
How to Count the Number of Words in a String in Oracle
Using Variables in Plsql Select Statement
How to Merge Two or More Rows Based on Their Foreign Key
"Rolling Up" Groups in Jaspersoft Ireport
How to Do a Contiguous Group by in MySQL
How to Change All SQL Columns of One Datatype into Another
SQL Primary Key - Is It Necessary
Multiple Rows into a Single Row and Combine Column SQL