This documentation describes the integration of MindsDB with PostgreSQL, a powerful, open-source, object-relational database system. The integration allows MindsDB to access data stored in the PostgreSQL database and enhance PostgreSQL with AI capabilities.

This data source integration is thread-safe, utilizing a connection pool where each thread is assigned its own connection. When handling requests in parallel, threads retrieve connections from the pool as needed.

​
Prerequisites

Before proceeding, ensure the following prerequisites are met:

  1. Install MindsDB locally via Docker or Docker Desktop.
  2. To connect PostgreSQL to MindsDB, install the required dependencies following this instruction.

​
Connection

Establish a connection to your PostgreSQL database from MindsDB by executing the following SQL command:

CREATE DATABASE postgresql_conn 
WITH ENGINE = 'postgres', 
PARAMETERS = {
    "host": "127.0.0.1",
    "port": 5432,
    "database": "postgres",
    "user": "postgres",
    "schema": "data",
    "password": "password"
};

Required connection parameters include the following:

  • user: The username for the PostgreSQL database.
  • password: The password for the PostgreSQL database.
  • host: The hostname, IP address, or URL of the PostgreSQL server.
  • port: The port number for connecting to the PostgreSQL server.
  • database: The name of the PostgreSQL database to connect to.

Optional connection parameters include the following:

  • schema: The database schema to use. Default is public.
  • sslmode: The SSL mode for the connection.

​
Usage

The following usage examples utilize the connection to PostgreSQL made via the CREATE DATABASE statement and named postgresql_conn.

Retrieve data from a specified table by providing the integration name, schema, and table name:

SELECT *
FROM postgresql_conn.table_name
LIMIT 10;

Run PostgreSQL-native queries directly on the connected PostgreSQL database:

SELECT * FROM postgresql_conn (

    --Native Query Goes Here
     SELECT 
        model, 
        COUNT(*) OVER (PARTITION BY model, year) AS units_to_sell, 
        ROUND((CAST(tax AS decimal) / price), 3) AS tax_div_price
    FROM used_car_price

);

Next Steps

Follow this tutorial to see more use case examples.

​
Troubleshooting

Database Connection Error

  • Symptoms: Failure to connect MindsDB with the PostgreSQL database.
  • Checklist:
    1. Make sure the PostgreSQL server is active.
    2. Confirm that host, port, user, schema, and password are correct. Try a direct PostgreSQL connection.
    3. Ensure a stable network between MindsDB and PostgreSQL.

SQL statement cannot be parsed by mindsdb_sql

  • Symptoms: SQL queries failing or not recognizing table names containing spaces or special characters.
  • Checklist:
    1. Ensure table names with spaces or special characters are enclosed in backticks.
    2. Examples:
      • Incorrect: SELECT * FROM integration.travel data
      • Incorrect: SELECT * FROM integration.‘travel data’
      • Correct: SELECT * FROM integration.`travel data`