Psql idioms

Postgres idioms to save you time

Curators only

cleanup note after load:

update note set sub_id_fk=replacement_fk from retired where sub_id_fk=old_id;

Closest purchasable metabolite

select s.sub_id, s.smiles, tanimoto_sml(f.data,    bfp_from_binary_text('\x804300004000200080400000800000008220800040000120008002002011000010040081010000002840400009d040048000020200001a404100000002000040')), short_name from fingerprints.ecfp4_data as f join fingerprints.ecfp4_id on ecfp4_id=f.ecfp4_fk join substance as s on s.ecfp4_fk=ecfp4_id join catalog_item on sub_id_fk=s.sub_id join catalog on cat_id_fk=cat_id where f.ecfp4_fk < 1000000 and purchasable > 30 order by bfp_from_binary_text('\x804300004000200080400000800000008220800040000120008002002011000010040081010000002840400009d040048000020200001a404100000002000040') <%> f.data limit 5;

select max(ecfp4_fk) from fingerprints.ecfp4_data

Sysadmins only

SELECT * from pg_stat_activity ;
SELECT * from pg_stat_activity where state = 'active';
pg_cancel_backend(pid int) to kill