PostgreSQL How to query String array

Question

I am trying to write a query to check if an element is within an array of Strings.

Here is my simple select query along with the output

select languages from person limit 3;
{CSS,HTML,Java,JavaScript,Python}
{JavaScript,Python,TensorFlow}
{C++,Python}

How do I write a query to find all people who have "Java" as a listed language they know?
I tried following the syntax but it isn't working.

select languages from person where languages @> ARRAY['Java']::varchar[];

Getting this error - cannot resolve 'Java' given input columns: — Sandy
– Sandy, Commented Mar 22, 2019 at 6:40

user330315 · Accepted Answer · 2019-03-22 06:43:59Z

13

You need to use a string constant on the left side, and the ANY operator on the array column:

select languages 
from person 
where 'Java' = any(languages);

This assumes languages is defined as text[] or varchar[] as your sample output indicates

answered Mar 22, 2019 at 6:43

user330315

Sign up to request clarification or add additional context in comments.

Comments

hci · Accepted Answer · 2021-11-03 14:55:16Z

3

You can search for more than one pattern replacing '=' operator by the regular expression match operator '~' preceding by a POSIX regular expression, such as:

select languages from person where '[Java,Php]' ~ ANY (string_to_array(languages , ','))

answered Nov 3, 2021 at 14:55

hci

393 bronze badges

Comments

Jophy job · Accepted Answer · 2019-03-22 06:43:10Z

2

try this

select languages from person where 'Java' = ANY (string_to_array(languages , ','))

answered Mar 22, 2019 at 6:43

Jophy job

1,9842 gold badges25 silver badges43 bronze badges

Collectives™ on Stack Overflow

PostgreSQL How to query String array

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related