Postgres: ANY function does not work on varchar array-CodePudding

I have a "product" table with a varchar[] column to keep excluded companies.

When I select the array for a specific product, I get it like this:

SELECT excluded_company_codes FROM product WHERE code = '123'
excluded_company_codes
----------
{'10'}

However, oddly enough, when I try to check if company code exists in the array with ANY function, it doesn't work:

SELECT '10'=ANY(product.excluded_company_codes) where code = '123'
?column?
----------
false

What am I doing wrong here?

CodePudding user response：

The string in the array contains two single quotes. If you want to find that, you have to

SELECT '''10''' = ANY(product.excluded_company_codes)
FROM product
WHERE code = '123';

If you want to avoid doubling the quotes, you can use dollar quoting: $$'10'$$.