Insert a new molecule in the database with cartridge

[ValeryPolyakov]

In the example described here: https://www.rdkit.org/docs/Cartridge.html, where you have two tables rdk.mols and rdk.fps, how do I insert a new molecule and make sure that it is indexed and could be found via an appropriate SQL query.

Can you list a sequence of SQL command for insertion?

Thanks,

Valery

[greglandrum]

Hi @ValeryPolyakov, If you insert a molecule in a table with an index on the molecule column, the molecule will be automatically indexed.
Here's a simple demonstration of that:

regression=# create table mols (idx int,m mol);
CREATE TABLE
regression=# create index molidx on mols using gist(m);
CREATE INDEX
regression=# insert into mols values (1,'CCO'::mol);
INSERT 0 1
regression=# insert into mols values (2,'CCN'::mol);
INSERT 0 1
regression=# insert into mols values (3,'CCC'::mol);
INSERT 0 1
regression=# select * from mols where m@>'CCN';
 idx |  m  
-----+-----
   2 | CCN
(1 row)
regression=# select * from mols where m@>'CC';
 idx |  m  
-----+-----
   1 | CCO
   2 | CCN
   3 | CCC
(3 rows)

[ValeryPolyakov]

Hi @greglandrum, In this example, how do I include added molecules in similarity searches? Do I need to add anything to the rdk.fps table?

[greglandrum]

Yes, you need to also insert the new fingerprint into the fingerprint table.
You can do this manually, with insert commands as shown above for the molecules, or you can do it more simply with a query.
Continuing my example above, if I create a fingerprint table with this command (from the rdkit cartridge docs):

regression=# select idx,torsionbv_fp(m) as torsionbv,morganbv_fp(m) as mfp2,featmorganbv_fp(m) as ffp2 into fps from mols;
SELECT 3

and then insert a couple of new rows into my molecule table:

regression=# insert into mols values (4,'C1CC1'::mol);
INSERT 0 1
regression=# insert into mols values (5,'C1CC1O'::mol);
INSERT 0 1

I can add new rows to the fingerprint table for each idx that isn't already there with this insert command:

regression=# insert into fps select idx,torsionbv_fp(m) as torsionbv,morganbv_fp(m) as mfp2,featmorganbv_fp(m) as ffp2 from (select idx,m from mols left outer join fps using (idx) where mfp2 is null) tmp;
INSERT 0 2

The key here is the left outer join in the subquery which identifies the rows from the mols table which aren't in the fps table.

If you plan to regularly update the molecule table and want this done automatically, it would be best to create a trigger (https://www.postgresql.org/docs/current/sql-createtrigger.html), but that's getting into a more advanced area.

[ValeryPolyakov]

Hi Greg, thanks a lot! Valery

[ValeryPolyakov]

Thanks Greg, Everything works well.