7 More Useful SQL and/or Postgres Techniques

1) Returning structured data from relations with json_agg

Let’s say we have an outfit and we want to return its id and a json string with data from all of that outfit’s associated items.

The following shows an example where you only want a subset of the items' columns:

select outfits.id,
       (select json_agg((select item_stub
                                      from (select items.name,
                                                   items.price) item_stub))
        from outfit_items
        join items on items.id = outfit_items.item_id
        where outfit_items.outfit_id = outfits.id
      ) as items
from outfits;

2) Querying an array field’s length.

Assuming tags is a flat, one dimensional array, how many outfits have any tags?

select count(outfits)
where array_length(tags, 1) is null

3) Counting conditionally with case

select sum(case when mobile = true then 1 else 0 end) as mobile_count
from impressions;

4) Set-like array append (no duplicates)

Create an array_append_distinct function with

CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyarray)
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT unnest($1) union SELECT unnest($2))
$$ LANGUAGE sql;

Then use it like this:

update items set tags = array_append_distinct(tags, CAST(ARRAY['foo','bar'] as text[]))

5) Removing duplicate associations from a join table

delete from outfit_items where id in (
  select o1.id
  from outfit_items o1
  join outfit_items o2
    on o1.outfit_id = o2.outfit_id
    and o2.item_id = o1.item_id
  where o1.id != o2.id);

6) Get column names for a table

select column_name
from information_schema.columns
where table_name='items'

7) Get list of all databases and their users

In psql: \l