How to find string values in text array using SQL query.
Suppose I have:
id location
1 {Moscow,New york}
2 {Mumbai}
3 {California,Texas}
I want to find id whose location is Moscow.I used:
select id from table where location in ('Moscow'); but get error:
ERROR: malformed array literal: "Moscow"
LINE 1: select id from table where location in ('Moscow');
DETAIL: Array value must start with "{" or dimension information.
I am using Postgres.
For DataType=Array, you can use the method=Any.
select id from table where 'Moscow' = Any(location)
As the document which describes DataType=Array:
8.14.5. Searching in Arrays
To search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array.
or use the method = Any:
9.21.3. ANY/SOME (array)
expression operator ANY (array expression) expression operator SOME (array expression) The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).
For Searching in Array DataType you can use the ANY()
SELECT id FROM table WHERE 'Moscow' = ANY(location);
Live Demo
text[]
select id from demo where location like '%Moscow'%'
text[]