PostgreSQL – How to Check if a Value is in an Array in a Table

arraypostgresqlpostgresql-11

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?

Best Answer

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:

Related Question