How to check, is (value) in postgres sql (array)?

Question

I have an array in SQL like

CREATE TABLE Table1 (
examples text ARRAY);

INSERT INTO Table1 
(examples)
VALUES
{t1,t2,t3};

I can't find a description of the correct syntax to do select * from Table1 where (values) in (any\some) examples I tried the tutorial example but it doesn't work or I just don't understand something

select *
from player_scores
where 95 < any (round_scores);

Do I understand correctly that you need to do something like this -

Postgresql Select rows where column = array or this - How to check if any field of array not contains substring in Postgres?

SELECT * FROM Table1 WHERE "value" = any (examples);

You need to use single quotes instead of double quotes dbfiddle.uk/oiD2UpVP — SelVazi
– SelVazi, Commented Oct 13, 2023 at 9:23

Albina · Accepted Answer · 2023-10-13 09:26:29Z

1

DB fiddle

Your query should be:

select *
from player_scores
where '95' = ANY(round_scores);

You can check this answer too.

answered Oct 13, 2023 at 9:26

Albina

2,0213 gold badges9 silver badges20 bronze badges

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

Comments

Zufar Sunagatov · Accepted Answer · 2023-10-13 09:41:37Z

0

How to check, is (value) in postgres sql (array)?

Yes, it is correct. if you want to check whether a value is in a PostgreSQL array, you can use function ANY(). ANY() returns true if the value is present in the array, and false otherwise.

Furthermore you need to use single quotes instead of double quotes when using ANY() with arrays in PostgreSQL. The reason is ANY() expects the array elements to be enclosed in single` quotes:

SELECT * FROM player_scores WHERE '95' = ANY(round_scores);

edited Oct 13, 2023 at 9:41

answered Oct 13, 2023 at 9:27

Zufar Sunagatov

1,3261 gold badge8 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to check, is (value) in postgres sql (array)?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related