I have a table like :
CREATE TABLE my_table
(
id bigint not null,
available_region_code character varying(255)[],
is_valid bigint,
)
I want usearray_length(), cardinality() or available_region_code ='{}' to select empty available_region_code columns but it failed, the length return one.
Why does this happen and how to solve this problem.
I don't know what reason causes this problem. Maybe like jjanes say in the comments the array has a non-printing character.
In the last, I use (CAST(ARRAY_TO_JSON(available_region_code) AS VARCHAR) IN ('[null]', '[""]')) to check empty array which learn from answer of Zone in question(How to check if an array is empty in Postgres).
The result on blow:
for a similar issue I had in presto based SQL, I created a case statement like:
case when available_region_code[1] = '' then 0 else cardinality(available_region_code) end as cardinality
Not sure if this helps in your use case, but it resulted in 0's for my empty arrays, and the correct counts for all others.
available_region_code[1] IS NULL?available_region_code[1] IS NULLis Fase, maybe like @jjanes says, there are non-printing characters in the array.available_region_code[1]::bytea. That will show you the bytes stored.