JChem PostgreSQL Cartridge FAQ and Known Issues


    How to cut off data fields from structures stored in sdf format

    In case you want to remove the data fields from the structures stored in sdf format, the following UPDATE statement can be helpful:

    UPDATE <table> SET <structure_column> = substring(<structure_column>::text,'^.*M\ \ END')::molecule('molecule_subtype');


    UPDATE mytable SET mol = substring(mol::text,'^.*M\ \ END')::molecule('sample');

    How to apply different business rules on the same chemical structure dataset

    The following business rules can be defined in the molecule type files.

    • standardizer configuration
    • assuming absolute or relative stereochemistry
    • switching tautomer search on or off
    • fingerprint configuration

    If you want more than one combination of the above listed business rule parameters be available - for example you want to run searches with and without tautomer search - you have to create separate table columns with different molecule types. Unfortunately, this means duplication, multiplication of the stored chemical structure data.

    How to install extensions to different schema than public

    By default, the extensions chemaxon_type and chemaxon_framework are installed into the PUBLIC schema of the PostgreSQL database. However, there can be cases when these extensions must be installed into a different schema. For example, if there is an object - like a table - named to molecule already present in the PUBLIC schema.

    The following steps should be executed:

    CREATE SCHEMA chemaxon;
    CREATE EXTENSION chemaxon_type WITH SCHEMA chemaxon;
    CREATE EXTENSION chemaxon_framework WITH SCHEMA chemaxon;
    CREATE EXTENSION hstore;  -- this extension does not need to be installed into the chemaxon schema

    In order to make searches able to use these extensions, search_path must be modified.

    Session level modification:

    SET search_path TO chemaxon,public;

    Database level modification:

    ALTER DATABASE <db_name> SET search_path TO chemaxon,public;

    See further possibilities in PostgreSQL documentation about schema search path.

    How to install HSTORE extension

    JPC can only be successfully installed if the


    step of the installation process can be executed.

    HSTORE extension is part of the contrib package of PostgreSQL database (for example postgresql-contrib-12), so it is necassary to have the contrib package installed.

    Known Issues

    False similarity search results in the case of molecule types with tautomer=GENERIC parameter

    In the case molecule types with tautomer=GENERIC parameter, similarity search gives false results. These is no workaround at the moment, please do not execute similarity search in structure columns having molecule type with tautomer=GENERIC parameter. From version 21.9.0 similarity search works correctly even in the case of molecule types with tautomer=GENERIC parameter.