Cacher is the code snippet organizer for pro developers

-- pg dump of a specific schema from metrics db on index
pg_dump metrics -h 10.1.24.39 -p 5433 -U cdt_user -n <schema_name> > <out>.sql

-- select a random percentage of rows of table
select * from my_table tablesample bernoulli(<percentage in numbers, e.g. 10>)
-- or to select random rows in query
select * from query order by random() limit 100

-- useful aggregate functions to use with GROUP BY
string_agg(<field>, ';') /* equivalent to R's paste(<field>, collapse = ";") */

-- numbering rows by grouping
select participant_id
    ,row_number() over(partition by participant_id) as row_index
from blahdiblah
 
-- time functions
<some_date> > now() - interval '9 month' -- get only rows from last 9 months
date(date_trunc('week', <some_date>)) -- round down to week of some_date