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.