I want to add a where condition if the item exist in array, how to make this conditional statement?
SELECT
*
FROM
table
WHERE
id = 'XX'
AND
tag in {'one', 'two', 'three'};
2 Answers
You're almost there! Just replace { with (.
Let's test by creating table first:
create table test(
id varchar(2),
tag varchar(10)
);
Insert some values:
insert into test(id, tag) values ('XX', 'one'), ('YY', 'two'), ('XX', 'three'), ('XX', 'four');
Execute your query:
SELECT
*
FROM
test
WHERE
id = 'XX'
AND
tag in ('one', 'two', 'three');
You should see the result as below:
id|tag |
--+-----+
XX|one |
XX|three|
Comments
There are various ways, depending on what exactly you want to do and what type is the column you want to query: string, integer, enum, json...
Basically I have only used in (...) notation, but I found way with using any as well.
If your column (myColumn) is an array, you can use this
SELECT * FROM myTable
WHERE 'myStringValue/enum' = ANY (myColumn)
But if your column (myColumn) is not an array (scalar), you can use this
SELECT * FROM myTable
WHERE myColumn IN (...values)
Some reference to read on your own: https://www.postgresql.org/docs/14/arrays.html#ARRAYS-SEARCHING and https://www.postgresql.org/docs/14/functions-comparisons.html
tag in ('one', 'two', 'three');