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.


    Name text PRIMARY KEY,
    List text[]

INSERT INTO public."HelloWorldTable" (name, list) VALUES ('HelloWorldList', ARRAY['Hello', 'World']);

DO $$
  IF 'Hello' = ANY (ARRAY['Hello', 'World']::text[]) THEN
      RAISE NOTICE 'Found';
      RAISE NOTICE 'Not found';

-- 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?

  • 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
    Dec 10, 2019 at 15:52
  • 1
    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
    Dec 10, 2019 at 15:52
  • 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
    Dec 10, 2019 at 15:54
  • 1
    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)
    – user1822
    Dec 10, 2019 at 15:55
  • Note: I removed the redundant UNIQUE constraint, orthogonal to the problem. (And PK columns are NOT NULL automatically.) Dec 11, 2019 at 18:52

1 Answer 1


Use instead:

DO $do$
      SELECT FROM hello_world_table
      WHERE  name = 'HelloWorldList'
      AND    'Hello' = ANY (list)
      ) THEN 
       RAISE NOTICE 'Found';
      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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.