How to check if a value is in an array in a table
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&lt;>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