Psql idioms
Jump to navigation
Jump to search
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;
load chembl
- 1. grant all on database stage to test;
- 2. sed 's/SET search_path = public,/SET search_path = chembl20,/' chembl_20.pgdump.sql >> install2
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
select count(*) from fingerprints.ecfp4.data;
select max(ecfp4_id) from fingerprints.ecfp4.data
\dt fingerprints.ecfp4.data
update traits column of substance
zinc15=> update substance as n set features=uniq(sort(n.features + intset(202^C) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.np>1 and o.sub_id=n.sub_id; zinc15=> \timing Timing is on. zinc15=> update substance as n set features=uniq(sort(n.features + intset(202))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.np>1 and o.sub_id=n.sub_id; UPDATE 23515 Time: 284042.158 ms zinc15=> update substance as n set features=uniq(sort(n.features + intset(203))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.np>0 and o.sub_id=n.sub_id; ^[[AUPDATE 107856 Time: 672273.171 ms zinc15=> update substance as n set features=uniq(sort(n.features + intset(211))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.drug=10 and o.sub_id=n.sub_id; UPDATE 3270 Time: 10765.466 ms zinc15=> update substance as n set features=uniq(sort(n.features + intset(212))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.drug>7 and o.sub_id=n.sub_id; UPDATE 9241 Time: 397424.032 ms zinc15=> update substance as n set features=uniq(sort(n.features + intset(213))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.drug>5 and o.sub_id=n.sub_id;
And aggregators:
update substance as n set features=uniq(sort(n.features + intset(220))) from substance as o join catalog_item on o.sub_id=sub_id_fk join catalog on cat_id_fk=cat_id where catalog.cat_id=1 and o.sub_id=n.sub_id;
Loading Chembl
grant all privileges on table gene2 to test; grant all privileges on table annotation2 to test; grant all privileges on table note2 to test;