rdkit

pig repo add pgdg -u    # add pgdg repo and update cache

pig install rdkit;		# install via package name, for the active PG version

pig install rdkit -v 18;   # install for PG 18
pig install rdkit -v 17;   # install for PG 17
pig install rdkit -v 16;   # install for PG 16
pig install rdkit -v 15;   # install for PG 15
pig install rdkit -v 14;   # install for PG 14

CREATE EXTENSION rdkit;

CREATE EXTENSION rdkit;

-- Create molecule from SMILES
SELECT 'c1ccccc1'::mol;

-- Check if SMILES is valid
SELECT is_valid_smiles('c1ccccc1');

-- Convert molecule to SMILES
SELECT mol_to_smiles('c1ccccc1'::mol);

-- Substructure match operator
SELECT 'c1ccccc1O'::mol @> 'c1ccccc1'::mol;   -- true (phenol contains benzene)
SELECT 'c1ccccc1'::mol <@ 'c1ccccc1O'::mol;    -- true

-- Using SMARTS patterns
SELECT 'c1ccccc1O'::mol @> 'c1ccc(O)cc1'::mol;

-- Tanimoto similarity (returns value between 0 and 1)
SELECT tanimoto_sml(morganbv_fp('c1ccccc1'::mol), morganbv_fp('c1ccccc1O'::mol));

-- Dice similarity
SELECT dice_sml(morganbv_fp('c1ccccc1'::mol), morganbv_fp('c1ccccc1O'::mol));

-- Morgan fingerprint (bit vector)
SELECT morganbv_fp('c1ccccc1'::mol);
SELECT morganbv_fp('c1ccccc1'::mol, 2);  -- radius=2

-- RDKit fingerprint
SELECT rdkit_fp('c1ccccc1'::mol);

-- Topological torsion fingerprint
SELECT torsionbv_fp('c1ccccc1'::mol);

-- Atom pair fingerprint
SELECT atompairbv_fp('c1ccccc1'::mol);

SELECT mol_amw('c1ccccc1'::mol);          -- average molecular weight
SELECT mol_logp('c1ccccc1'::mol);         -- LogP
SELECT mol_hba('c1ccccc1O'::mol);         -- H-bond acceptors
SELECT mol_hbd('c1ccccc1O'::mol);         -- H-bond donors
SELECT mol_numrotatablebonds('c1ccccc1'::mol); -- rotatable bonds
SELECT mol_numatoms('c1ccccc1'::mol);     -- number of atoms
SELECT mol_numheavyatoms('c1ccccc1'::mol);    -- heavy atoms
SELECT mol_numrings('c1ccccc1'::mol);     -- number of rings

-- Substructure search index
CREATE INDEX idx_mol ON molecules USING gist(molecule);

-- Fingerprint similarity index
CREATE INDEX idx_fp ON molecules USING gist(morganbv_fp(molecule));

-- Substructure search
SELECT * FROM molecules WHERE molecule @> 'c1ccccc1'::mol;

-- Similarity search (with threshold)
SET rdkit.dice_threshold = 0.5;
SELECT * FROM molecules WHERE morganbv_fp(molecule) % morganbv_fp('c1ccccc1O'::mol);