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 Steps

Check 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`
PlanetScaleQuestDB