To check how many rows have an empty array of values for a certain column,
Such as to "select from table where dataset is not null";
by dataset it could be array of values. for instance another select query could be a dataset
e.g. (array(select from table2))
See an illustration below. Hope it helps.
so lets say you have two tables for customers and their favorite foods
customer
ID | name
---------
1 stanley
2 sheldon
3 sarah
favorite_food
ID | customer_id(FK to customer ID)|food_name
------------------------------------------------------------------------
1 3 rice
2 3 wheat
3 1 mango
4 2 orange
--the following query will help to make get all instances of customers with food
--this is based on the fact that array_upper and array_lower functions, on empty arrays return null
select * from customer where array_upper(array(select food_name::text
from favorite_food
where customer_id = customer.id),1) is not null;
--there are other alternative to this solution like the example below.
--In the alternative below "{}" is s string literal that is returned when there are no values in the csv table
select * from customer where not (array(select food_name::text
from favorite_food
where customer_id = customer.id),1) = '{}';
--third alternative is to use regular expressions and this can be done in several ways besides what is shown below;
Feel free to add your options are comment or ask a question ;)
select * from customer where not array_to_string(array(select food_name::text
from favorite_food
where customer_id = customer.id),1) ~ '^$';
--of course you can try to remove the "not" to get the opposite result you know what we do.hope this saves someone some time.
