PostgreSQL

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 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

);

​

Troubleshooting