Psql idioms
Jump to navigation
Jump to search
Table locks?
select * from admin_postgresql_locks; SELECT * from pg_stat_activity where state = 'active'; pg_cancel_backend(pid int) to kill
Table cluster status. (correlation).
select tablename,attname,correlation from pg_stats where tablename like '%sub%' order by correlation limit 20;
Curators only
= update purchasability by catalog
begin; update substance as s set purchasability = substance_best_purchasability(s.sub_id) from catalog_item as ci join catalog as c on c.cat_id=ci.cat_id_fk where s.sub_id = ci.sub_id_fk and c.short_name = 'sialbb'; commit;
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;
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;
islikea metabolite (still to be implemented)
SELECT substance.* FROM substance JOIN catalog_item ON sub_id=sub_id_fk JOIN catalog ON cat_id_fk=cat_id where short_name='sial' AND sub_id IN (SELECT s2.sub_id FROM substance AS s1 JOIN fingerprints.ecfp4_data AS d1 ON s1.ecfp4_fk=d1.ecfp4_fk JOIN fingerprints.ecfp4_data as d2 ON d1.data % d2.data JOIN substance AS s2 ON d2.ecfp4_fk=s2.ecfp4_fk WHERE s1.features @> '{201}' AND d1.ecfp4_fk < 500000) LIMIT 50;
how to fix a text field
update pattern set name = lower(replace(replace(replace(description,'(','_'),')','_'),' ','-'));
SMARTS
select sub_id,smiles from substance where smiles @> '[#15]~[#15,#7,#16]'::qmol";
FREE
alter user zincfree set search_path to "$user",public
undo:
alter user zincfree set search_path to "$user",free, public
create new condition sets
select * from clinical1.ct2condition where to_tsvector('english',name) @@ to_tsquery('depression|anxiety|schizophrenia'); update clinical1.ct2condition set condclass_fk=1 where condclass_fk=2; update clinical1.ct2condition set condclass_fk = 2 where condclass_fk = 0 and to_tsvector('english',name) @@ to_tsquery('depression');