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.

multiple listbox select to get a comma separated value

So you have a list box with multiple selects and you want to get a commma separated value of selected values when the form is submited
It is simple html and some php.

example code:
<form action="" method="POST">
<select multiple name="mypets[]">
    <option value="dog">Dog</option>
    <option value="cat">Cat</option>
    <option value="bird">Bird</option>
    <option value="mouse">Mouse</option>
</select>

<input type=submit value=submit></input>
</form>

<?php

$mypets = isset($_POST['mypets'])? $_POST['mypets'] : array();
$pets_csv = implode(',',$mypets);

echo($pets_csv);
?>


explantion:
not there that he emphasis is on the "[]" put on front of the name of the list box
thus we are naming it "mypets[]" instead of say "mypets" so that values are stored in the post variable as and array
The as usually you can use implode to get the comma separated values accordingly.
Hope this helps someone who especially overlooks to add "[]" to the name.

implode() notice: php array to string conversion

the general format of the implode() function are as follows:
i)    string implode ( $separator, $array)
ii)    string implode (  $array)
iii)    string implode ( $array, $separator)
I faced a little problem when “$array” wasn’t just an indexed array
but an associative array (thus array of arrays).
I posted this so I can help someone save 10mins
Solution:
if you have an associative array say $array,
use array_values() as shown in the example below:
implode ( ‘,’, array_values($array))
hope it helps!
S

List all foreign keys references for a given table- pg_catalog and information schema

When you want to get all foreign key references of a table’s id. There are 2 options you can employ when you are using postgres.
1. This script one will only work if you have been assigning unique names to foreign key in your database:
SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints  tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = ‘FOREIGN KEY’ AND ccu.table_name= the_name_of_the_table

2. This second script will work for all cases. you just have to supply the table in context.
select (select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=m.relnamespace) as target_ns,
m.relname as target_table,
(select a.attname from pg_attribute a where a.attrelid = m.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname, o.conname as target_constraint_name from pg_constraint o left join    pg_class c on c.oid = o.conrelid
left join pg_class f on f.oid = o.confrelid left join pg_class m on m.oid = o.conrelid
where o.contype = ‘f’ and o.conrelid in (select oid from pg_class c where c.relkind = ‘r’)  and f.relname = the_name_of_the_table


I do not have the luxury of time to explain in details but lemme know if you have any questions or addition. Will be happy to discuss this.