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:
- Install MindsDB locally via Docker or Docker Desktop.
- 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:
- Make sure the PostgreSQL server is active.
- Confirm that host, port, user, schema, and password are correct. Try a direct PostgreSQL connection.
- 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:
- Ensure table names with spaces or special characters are enclosed in backticks.
- Examples:
- Incorrect: SELECT * FROM integration.travel data
- Incorrect: SELECT * FROM integration.‘travel data’
- Correct: SELECT * FROM integration.`travel data`
Was this page helpful?