Psql idioms

From DISI
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
 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;

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

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