Docs

RDS for PostgreSQL

Extensions

Chemical molecular calculation retrieval (RDKit)

RDKit is an open-source cheminformatics toolkit that uses machine learning methods to generate compound fingerprints for compound substructure searches and structural similarity calculations.

Usage restrictions

Supports the use of RDKit extension only in PostgreSQL 13 and above versions, see Supported extension list for the version of extension needed to be used in each version instance.

Extension usage

Create and delete extension

Create extension

tip

A super account is required to create the extension.

CREATE EXTENSION rdkit;

Delete extension

DROP EXTENSION rdkit;

Data type

Data type description

The basic data types supported by the extension are mol, qmol, bfp, sfp, and reaction. The meanings of different types are as follows:

mol is a commonly used chemical molecule format, usually used to save a single molecule.

qmol is a chemical molecule data type containing query features provided by RDKit, which can be directly transformed from formats such as SMARTS to qmol type data, for example ‘c1cccc[c,n]1’::qmol.

bfp is a bit vector form of chemical molecular fingerprint type provided by RDKit.

sfp is a sparse vector form of chemical molecular fingerprint type provided by RDKit.

reaction is a chemical reaction template type provided by RDKit.

Data type conversion

Convert SMILES format molecules into mol type and insert into the table.

insert into mols values
(1, 'C1=CC=C\\2C(=C1)C=CC(=O)/C2=N\\NC3=NC=CS3'::mol),
(2, 'CC1=C(N=CC=C1)NC2=NC(=CS2)C3=CC=CC=N3'::mol),
(3, 'CC(=NNC1=C(C=C(C=N1)Cl)Cl)C2=CC=CC=N2'::mol),
(4, 'CC(=NNC(=S)NCC=C)C1=CC=CC=N1'::mol),
(5, 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);

Convert SMARTS format molecules into qmol type.

select 'c1[o,s]ncn1'::qmol;

Use fingerprint calculation function to convert mol type data into sfp type data, and insert into the table.

select id, torsion_fp(m) as tfp, morgan_fp(m) as mfp, featmorgan_fp(m) as ffp into sfps from mols;

Use fingerprint calculation functions to convert mol type data into bfp type data, and insert them into the table.

select id, torsionbv_fp(m) as tfp, morganbv_fp(m) as mfp, featmorganbv_fp(m) as ffp into bfps from mols;

Convert the SMARTS format chemical reaction template into a reaction object (chemical reaction engine object).

SELECT reaction_from_smarts('c1ccc[n,c]c1>>c1nccnc1');

Operators

This topic uses mol type and bfp type as examples to explain the meaning and usage of operators, the meaning and usage of each operator under other data types are basically consistent with mol type and bfp type, this topic will not list them one by one. Refer to RDKit user guide for more details.

mol type

mol type supports substructure query operators (<@, @>, @=) and comparison operators (>, >=, <, <=, <>, =). The meanings and uses of various operators when handling mol type objects are as follows:

Operators	Note	Examples
<@	mol1 <@ mol2: Return true if mol1 is a substructure of mol2, otherwise return false.	-- Query the mols table for molecules containing the 'c1cccnc1' structure select * from mols where 'c1cccnc1'::mol <@ m; -- Query the molecules in the mols table that are substructures of 'c1cccnc1' select * from mols where m <@ 'c1cccnc1'::mol;
@>	mol1 @> mol2: Return true if mol2 is a substructure of mol1, otherwise return false.	-- Query the molecules in the mols table that are substructures of 'c1cccnc1' select * from mols where 'c1cccnc1'::mol @> m; -- Query the mols table for molecules containing the 'c1cccnc1' structure select * from mols where m @> 'c1cccnc1'::mol;
<@	qmol <@ mol: Return true if qmol is a substructure of mol, otherwise return false.	-- Query the mols table for molecules containing the 'c1cccnc1' structure select * from mols where 'c1cccnc1'::qmol <@ m;
@>	mol @> qmol: Return true if qmol is a substructure of mol, otherwise return false.	-- Query the mols table for molecules containing the 'c1cccnc1' structure select * from mols where m @> 'c1cccnc1'::qmol;
@=	mol1 @= mol2: Return true if mol1 and mol2 have the same structure, otherwise return false.	-- Query the mols table for molecules that have the same structure as 'C=CCNC(=S)NN=C(C)c1ccccn1' select * from mols where m @= 'C=CCNC(=S)NN=C(C)c1ccccn1'::mol;
>	mol1 > mol2: Return true if mol1 is greater than mol2; otherwise, return false.	-- Query the mols table for molecules greater than 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m > 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;
>=	mol1 >= mol2: Return true if mol1 is greater than or equal to mol2, otherwise return false.	-- Query the mols table for molecules greater than or equal to 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m >= 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;
<	mol1 < mol2: Return true if mol1 is less than mol2; otherwise, return false.	-- Query the mols table for molecules less than 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m < 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;
<=	mol1 <= mol2: Return true if mol1 is less than or equal to mol2, otherwise, return false.	-- Query the mols table for molecules that are less than or equal to 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m <= 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;
<>	mol1 <> mol2: Return true if mol1 does not equal mol2, otherwise return false.	-- Query the mols table for molecules that are not equal to 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m <> 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;
=	mol1 = mol2: Rreturn true if mol1 equals mol2, otherwise return false.	-- Query the mols table for molecules that are equal to 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1' select * from mols where m = 'CC(=NNc1ncc(Cl)cc1Cl)c1ccccn1'::mol;

bfp type

bfp type supports similarity query operators (%, <%>, #, <#>) and comparison operators (>, >=, <, <=, <>, =). The meanings and usages of different operators when operating on bfp type objects are shown in the following table:

Operators	Note	Examples
%	bfp1 % bfp2: Return true if the Tanimoto similarity of bfp1 and bfp2 exceeds the threshold, otherwise return false.	-- Query the Tanimoto similarity between molecules in the bfps table and 'c1cccnc1'. select id, tanimoto_sml(morganbv_fp('c1cccnc1'::mol) , mfp) from bfps; -- Query whether the Tanimoto similarity between molecules in the bfps table and 'c1cccnc1' exceeds the threshold. select id, morganbv_fp('c1cccnc1'::mol) % mfp from bfps;
<%>	bfp1 <%> bfp2: Return the Tanimoto similarity distance between bfp1 and bfp2, commonly used for sorting.	-- Query the Tanimoto similarity distance between molecules in the bfps table and 'c1cccnc1' select id, morganbv_fp('c1cccnc1'::mol) <%> mfp from bfps order by morganbv_fp('c1cccnc1'::mol) <%> mfp;
#	bfp1 # bfp2: Return true if the Dice similarity between bfp1 and bfp2 exceeds the threshold, otherwise return false.	-- Query the Dice similarity with 'c1cccnc1' in the bfps table select id, dice_sml(morganbv_fp('c1cccnc1'::mol), mfp) from bfps; -- Check whether the Dice similarity between molecules in the bfps table and 'c1cccnc1' exceeds the threshold select id, morganbv_fp('c1cccnc1'::mol) # mfp from bfps;
<#>	bfp1 <#> bfp2: Return the Dice similarity distance between bfp1 and bfp2, usually used for sorting.	-- Query the Dice similarity distance between molecules in the bfps table and 'c1cccnc1' select id, morganbv_fp('c1cccnc1'::mol) <#> mfp from bfps order by morganbv_fp('c1cccnc1'::mol) <#> mfp;
>	bfp1 > bfp2: Return true if bfp1 is greater than bfp2, otherwise return false.	-- Query the bfps table for molecules greater than 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp > morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);
>=	bfp1 >= bfp2: Return true if bfp1 is greater than or equal to bfp2; otherwise, return false.	-- Query the bfps table for molecules greater than or equal to 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp >= morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);
<	bfp1 < bfp2: Return true if bfp1 is less than bfp2, otherwise return false.	-- Query the mols table for molecules less than 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp < morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);
<=	bfp1 <= bfp2: Return true if bfp1 is less than or equal to bfp2; otherwise, return false.	-- Query the mols table for molecules that are less than or equal to 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp <= morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);
<>	bfp1 <> bfp2: Return true if bfp1 does not equal bfp2; otherwise, return false.	-- Query the mols table for molecules that are not equal to 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp <> morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);
=	bfp1 = bfp2: Return true if bfp1 equals bfp2; otherwise, return false.	-- Query the mols table for molecules equal to 'CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O' select id, mfp from bfps where mfp = morganbv_fp('CC1=CC=C(C=C1)C(=O)SC2=CC=CC=CC2=O'::mol);

Functions

RDKit supports a wide range of functions, but this section only provides a simple demonstration of their calling. For more detailed information, refer to the RDKit Usage Guide.

tanimoto_sml(bfp, bfp): Calculates Tanimoto similarity.

postgres=# select tanimoto_sml(morganbv_fp('c1cccnc1'::mol), morganbv_fp('c1cccnc1'::mol));
tanimoto_sml
--------------
1
(1 row)

postgres=# select tanimoto_sml(morganbv_fp('C1C(OC2=CC(=CC(=C2C1=O)))'::mol), morganbv_fp('c1cccnc1'::mol));
tanimoto_sml
---------------------
0.06666666666666667
(1 row)

dice_sml(bfp, bfp): calculates dice similarity.

postgres=# select dice_sml(morganbv_fp('c1cccnc1'::mol), morganbv_fp('c1cccnc1'::mol));
dice_sml
----------
1
(1 row)

postgres=# select dice_sml(morganbv_fp('C1C(OC2=CC(=CC(=C2C1=O)))'::mol), morganbv_fp('c1cccnc1'::mol));
dice_sml
----------
0.125
(1 row)

Index

RDKit supports btree index, gist index, and gin index. Below is a description of how to use various indexes, exemplified by the bfp type.

Create btree index

-- Create btree index
drop index if exists bfpidx;
create index bfpidx ON bfps(tfp);

Create gist index

-- Create gist index
drop index if exists bfpidx;
create index bfpidx on bfps using gist(tfp);

Create gin index

-- Create gin index
drop index if exists bfpidx;
create index bfpidx on bfps using gin(tfp);

References

RDKit user guide

Last updated: 2026.01.13 18:44:18