Skip to content

postgresql.mdx

Latest commit

 

History

History
105 lines (78 loc) · 3.49 KB

postgresql.mdx

File metadata and controls

105 lines (78 loc) · 3.49 KB
title PostgreSQL
sidebarTitle 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.

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`