Thursday, May 5, 2011

Postgres - How to check if an array is empty or not

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.

No comments:

Post a Comment