How to check if a value is in an array in a table

Question

I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I try to get the array from the table it does not work.

In the example below,
SELECT list FROM public."HelloWorldTable" WHERE name = 'HelloWorldList' selects the list element that contains an array that is identical to the one in the other if statement but I can not seem to access it the same.

MWE

CREATE TABLE IF NOT EXISTS "HelloWorldTable"(
 Name text PRIMARY KEY,
 List text[]
);
INSERT INTO public."HelloWorldTable" (name, list) VALUES ('HelloWorldList', ARRAY['Hello', 'World']);
DO $$
BEGIN 
 IF 'Hello' = ANY (ARRAY['Hello', 'World']::text[]) THEN
 RAISE NOTICE 'Found';
 ELSE
 RAISE NOTICE 'Not found';
 END IF;
-- failing attempt:
-- IF 'Hello' = ANY (SELECT list FROM public."HelloWorldTable"
-- WHERE name = 'HelloWorldList') THEN
-- RAISE NOTICE 'Found';
-- ELSE
-- RAISE NOTICE 'Not found';
-- END IF;
END $$;

I found this other question but I can not get it working the way I need it to. I have tried adding ::text and ::text[] to the second if statement but I only get different errors.
How can I solve this?

• user1822: Quote from the manual Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements
• user1822: Unrelated to your question, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/…
• user1822: It's a bit unclear to me what you are trying to achieve. Are you looking for a simple select * from helloworldtable where 'Hello' = any (list)?
• user1822: If you really need to store key/value pairs, a jsonb column would be a much better choice (provided the de-normalization makes sense to begin with)
• Erwin Brandstetter: Note: I removed the redundant UNIQUE constraint, orthogonal to the problem. (And PK columns are NOT NULL automatically.)

Answer 1

Use instead:

DO $do$
BEGIN 
 IF EXISTS (
 SELECT FROM hello_world_table
 WHERE name = 'HelloWorldList'
 AND 'Hello' = ANY (list)
 ) THEN 
 RAISE NOTICE 'Found';
 ELSE
 RAISE NOTICE 'Not found';
 END IF;
END $do$;

db<>fiddle here

With a subselect between the parentheses, like you tried, the ANY construct expects a set of element types, not an array. That would work with the column list being type text, not text[].

I can see how this is confusing, as Postgres automatically extracts the single value from a single column in a returned set in most other contexts to match the expected form. Like:

SELECT '{Hello,World}' = (SELECT list FROM hello_world_table WHERE name = 'HelloWorldList');

The untyped string literal '{Hello,World}' is even cast to text[] automatically, deriving the type from the subselect on the other side.

But in this case, the ANY construct is resolved to its alternative form expecting a set, which makes your attempt fail.

You could make it work by unnesting the array to produce the expected set:

SELECT 'Hello' = ANY (SELECT unnest(list) FROM hello_world_table
 WHERE name = 'HelloWorldList');

But don't. Use an EXISTS subquery instead and move the condition to the WHERE clause like demonstrated.

Related:

• How to use ANY instead of IN in a WHERE clause with Rails?
• Check if value exists in Postgres array
• PL/pgSQL checking if a row exists