These use cases show how you can use EDB Postgres Advanced Server with Chemaxon JChem PostgreSQL Cartridge.
Create table with MOLECULE datatype column
Using SQL, create the table in which a column has the MOLECULE datatype to store and process chemical data:
View the structure of this table:
Insert/update/delete chemical data
Using SQL statements, insert data, including chemical data, into the table:
Use a SELECT statement to retrieve the data from the table, including the chemical data in the column mo1:
Update and delete data from the table, including the chemical data:
Create indexes in molecule columns
For indexing a column containing chemical structures, the following indextypes are provided:
This example creates indexes in the Molecule columns on the table:
Searching for data from the Molecule column
You can perform substructure, duplicate, superstructure, and full-fragment searches.
Substructure search
Substructure search finds all structures that contain the query structure as a subgraph. Sometimes the chemical subgraph isn't the only thing that's provided. Certain query features that further restrict the structure are also provided. If special molecular features are present on the query, such as stereochemistry or charge, then only those targets match the ones that also contain the feature. However, if a feature is missing from the query, it doesn't need to be missing, by default.
Perform substructure search using the symmetrical sub-/super-structure search operator |<|.
This SQL statement creates a table and inserts data:
These SQL statements search data from the Molecule column:
Duplicate search
Use duplicate searches mainly before database inserts to check whether the given molecule is already in the database.
Perform duplicate searches using the |=| operator.
This SQL statement searches data from the Molecule column:
Superstructure search
Superstructure searches find all molecules for which the query is the superstructure of the target.
Perform superstructure searches using the sub-/super-structure search operator |>|.
This SQL statement searches data from the Molecule column:
Full-fragment (exact fragment) search
Full-fragment searches are between substructure searches and full searches. The query must fully match a whole fragment of the target. Other fragments might be present in the target, but they are ignored.
This SQL statement searches data from the Molecule column:
Import data using COPY
You can import data into the table that has a Molecule column using the COPY command. For example:
Create a .csv file containing comma-separated data:
Using this SQL statement, create the table:
Load data into the table:
You can then fetch data from the table using the following SQL statement: