How to declare a variable in a PostgreSQL query when the variable need to exists in an array

44 views Asked by At

How to declare a variable in a PostgreSQL query when the variable need to exists in an array, meaning my column is an array and I want to check if either one of my variable exists in that array.

My query:

DO $$
    DECLARE thetag1 text = 'tag1';
        thetag2 text = 'tag2';
BEGIN 
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
SELECT DISTINCT 
    p.id
FROM table_1 p
WHERE (thetag1 = ANY (p.tags::text[]))
      AND 
      (thetag2 = ANY (p.tags::text[]));
END $$;
SELECT * FROM tmp_table;
  • the column p.tags is charcater varying [] (255).
  • I got the messages: The specified object could not be found.
0

There are 0 answers