Psql idioms - Revision history

Frodo: asdf

2017-08-24T18:00:13Z

asdf

← Older revision		Revision as of 18:00, 24 August 2017
Line 1:		Line 1:
			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 ;
			# 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);		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);

Frodo at 21:30, 2 August 2017

2017-08-02T21:30:59Z

← Older revision		Revision as of 21:30, 2 August 2017
Line 1:		Line 1:
	Table locks?		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 text'''Table locks?
	select * from admin_postgresql_locks;		select * from admin_postgresql_locks;
	SELECT * from pg_stat_activity where state = 'active';		SELECT * from pg_stat_activity where state = 'active';

Frodo: asdf

2015-10-01T23:39:42Z

asdf

@@ Line 39: / Line 39: @@
   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');
 [[Category:Idioms]]

Frodo: asfd

2015-10-01T23:29:35Z

asfd

Show changes

Frodo: asdf

2015-09-09T17:59:46Z

asdf

← Older revision		Revision as of 17:59, 9 September 2015
Line 15:		Line 15:
	Table locks		Table locks
	select * from admin_postgresql_locks;		select * from admin_postgresql_locks;

			Create special indexes for bb and features
			create index concurrently ix_substance_smiles_bb on substance using gist (bb,smiles,sub_id) where bb = 1;
			create index concurrently ix_substance_smiles_interesting on substance using gist (features,smiles,sub_id) where features && array[201,202,203,210,211,212,213,214,215,216,220];

	Table clustering status. (correlation). Very cool. Close to zero on a big table means big payoff for clustering. But be careful about substance, takes 5 days!		Table clustering status. (correlation). Very cool. Close to zero on a big table means big payoff for clustering. But be careful about substance, takes 5 days!

Frodo: asfd

2015-08-14T23:00:57Z

asfd

← Older revision		Revision as of 23:00, 14 August 2015
Line 135:		Line 135:
	source env.csh		source env.csh
	zincserver.restart-backend.sh		zincserver.restart-backend.sh

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

Frodo: asdf

2015-07-28T21:48:01Z

asdf

← Older revision		Revision as of 21:48, 28 July 2015
Line 132:		Line 132:
	undo:		undo:
	alter user zincfree set search_path to "$user",free, public		alter user zincfree set search_path to "$user",free, public

			source env.csh
			zincserver.restart-backend.sh

Frodo: asdf

2015-07-28T21:45:15Z

asdf

← Older revision		Revision as of 21:45, 28 July 2015
Line 126:		Line 126:
	select count(hasring_id), ring_fk from hasring group by ring_fk order by count(hasring_id) desc;		select count(hasring_id), ring_fk from hasring group by ring_fk order by count(hasring_id) desc;
	[[Category:Idioms]]		[[Category:Idioms]]

			free non free

			alter user zincfree set search_path to "$user",public
			undo:
			alter user zincfree set search_path to "$user",free, public

Frodo: asdf

2015-07-15T19:29:04Z

asdf

← Older revision		Revision as of 19:29, 15 July 2015
Line 98:		Line 98:
	find things by smarts directly.		find things by smarts directly.
	psql -h samekh -U test zinc15 -c "select sub_id,smiles from substance where smiles @> '[#15]~[#15,#7,#16]'::qmol" >! b		psql -h samekh -U test zinc15 -c "select sub_id,smiles from substance where smiles @> '[#15]~[#15,#7,#16]'::qmol" >! b

			== corrected ==
			update pattern as p set n_biogenic = (select count(distinct(sp.sub_id_fk)) from subpat as sp joing substance as s on sp.sub_id_fk = s.sub_id where sp.pattern_fk =p.pattern_id and s.features @> intset(203} ; same as n_endometab 201

	update pattern stats		update pattern stats

Frodo: asdf

2015-07-15T17:36:23Z

asdf

← Older revision		Revision as of 17:36, 15 July 2015
Line 112:		Line 112:

	purchasable		purchasable
	update pattern as p1 set n_purchasable = (select count(distinct(sp.sub_id_fk)) from pattern as p2 join subpat as sp on sp.pattern_fk = p2.pattern_id where p1.pattern_id = p2.pattern_id and sp.sub_id_fk in (select sub_id as sub_id_fk from substance where purchasability >=10 and ~~sub_id~~ between 0 and ~~1000~~ ));		update pattern as p1 set n_purchasable = (select count(distinct(sp.sub_id_fk)) from pattern as p2 join subpat as sp on sp.pattern_fk = p2.pattern_id where p1.pattern_id = p2.pattern_id and sp.sub_id_fk in (select sub_id as sub_id_fk from substance where purchasability >=10 and p1.pattern_id between 1 and 10 ));

	all		all
	update pattern as p1 set n_total = (select count(distinct(sp.sub_id_fk)) from pattern as p2 join subpat as sp on sp.pattern_fk = p2.pattern_id where p1.pattern_id = p2.pattern_id and sp.~~sub_id_fk~~ between 0 and ~~1000~~);		update pattern as p1 set n_total = (select count(distinct(sp.sub_id_fk)) from pattern as p2 join subpat as sp on sp.pattern_fk = p2.pattern_id where p1.pattern_id = p2.pattern_id and p1.pattern_id between 1 and 10);

	rings - biogenic - not attempted yet.		rings - biogenic - not attempted yet.
	update rings as r1 set n_biogenic = (select count(distinct(hr.sub_id_fk)) from ring as r2 join hasring as hr on hr.ring_fk = r2.ring_id where r1.ring_id = r2.ring_id and hr.sub_id_fk in (select sub_id_fk from catalog_item where cat_id_fk in (select cat_id from catalog where np>0)));		update rings as r1 set n_biogenic = (select count(distinct(hr.sub_id_fk)) from ring as r2 join hasring as hr on hr.ring_fk = r2.ring_id where r1.ring_id = r2.ring_id and hr.sub_id_fk in (select sub_id_fk from catalog_item where cat_id_fk in (select cat_id from catalog where np>0)));

	ring statistics.		ring statistics.
	select count(hasring_id), ring_fk from hasring group by ring_fk order by count(hasring_id) desc;		select count(hasring_id), ring_fk from hasring group by ring_fk order by count(hasring_id) desc;
	[[Category:Idioms]]		[[Category:Idioms]]