Psql idioms

 select distinct subname.sub_id_fk,clinical2.ct2int.ct2int_id,subname.name,clinical2.ct2int.name from subname, clinical2.ct2int where subname.query@@clinical2.ct2int.terms and subname.sub_id_fk ; 

1. insert into join table to like CT to substances

begin; insert into synonym select nextval('synonym_synonym_id_seq'::regclass),excipient_id,'COMPOUND'::synonym_kinds,name from excipient where excipient.excipient_id not in (select excipient_id from synonym, excipient where excipient.name=synonym.value and excipient.excipient_id=synonym.excipient_fk);

Bold textTable 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;

= 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');

Maximum FP FK referenced from substance

select max(ecfp4_fk) from substance;

how many protomers

select count(prot_id) from protomer;

how many substances

select count(sub_id) from substance;

max FP reference in FP master join table:

select max(ecfp4_fk) from fingerprints.ecfp4_data