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.

​
Prerequisites

Before proceeding, ensure the following prerequisites are met:
  1. Install MindsDB and PostgreSQL on your system or obtain access to cloud options.
  2. To use PostgreSQL with MindsDB, install the required dependencies by running pip install mindsdb[postgres].

​
Connection

Establish a connection to your PostgreSQL database from MindsDB by executing the following SQL command: 
CREATE DATABASE psql_datasource 
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

Retrieve data from a specified table by providing the integration name, schema, and table name: 
SELECT *
FROM psql_datasource.schema_name.table_name
LIMIT 10;
Run PostgreSQL-native queries directly on the connected PostgreSQL database: 
SELECT * FROM psql_datasource (

    --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 demo_data.used_car_price

);
The above examples utilize psql_datasource as the datasource name, which is defined in the CREATE DATABASE command.
Next StepsCheck out the House Sales Forecasting tutorial, which uses the data from PostgreSQL connection to MindsDB.

​
Troubleshooting Guide

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`